Description:
This KB Article covers the Basic Knowledge about the SQL Queries. How to execute the simple queries in SQL Server Database.
QUERY 1: HOW TO CREATE NEW DATABASE?
CREATE DATABASE EMPLOYEE_DB
QUERY 2: HOW TO CONNECT TO ABOVE DATABASE?
USE EMPLOYEE_DB
QUERY 3: HOW TO CREATE NEW TABLE IN ABOVE DATABASE? WORKS ON ANY SERVER?
CREATE TABLE EMPLOYEE_INFO
(
EMP_ID INT, — this column stores DIGITS IN CRORES.
EMP_FNAME VARCHAR (30), — this column stores upto 30 CHARACTERS
EMP_LNAME VARCHAR (40), — this column stores upto 40 CHARACTERS
EMP_CNTRY CHAR(40), — this column stores upto 40 CHARACTERS
EMP_SAL BIGINT — this column stores DIGITS IN TRILLIONS.
)
QUERY 4: Insert Data
INSERT INTO EMPLOYEE_INFO VALUES (1001, ‘AMIN’, ‘A’, ‘CANADA’, 45678)
QUERY 5: Insert Data
INSERT EMPLOYEE_INFO VALUES (‘1002’, ‘JOHN’, ‘A’, ‘CANADA’, 643643),
(1003, ‘SAI’, ‘H’, ‘INDIA’, 36363),(1004, ‘SAISHA’, ‘T’, ‘INDIA’, NULL), (1005, ‘SAISH’, ‘G’, ‘INDIA’, 43643)
QUERY 6: WHAT IF WE DONT KNOW THE VALUE OF A COLUMN?
INSERT EMPLOYEE_INFO VALUES (1006, ‘AMIN’, ‘H’, ‘USA’, NULL)
QUERY 7: EXAMPLE FOR A BATCH?
INSERT EMPLOYEE_INFO VALUES (1007, ‘AMINI’, ‘L’, ‘USA’, 36363)
INSERT EMPLOYEE_INFO VALUES (1000, ‘TEST’, ‘ ‘, ‘INDIA’, NULL)
INSERT EMPLOYEE_INFO VALUES (1008, ‘TEST2’, ‘ ‘, ‘INDIA’, NULL)
QUERY 8: HOW TO REPORT / VERIFY ABOVE INSERTED DATA IN THE TABLE?
SELECT * FROM EMPLOYEE_INFO
QUERY 9: HOW TO REPORT / VERIFY ABOVE INSERTED DATA IN THE TABLE?
SELECT EMP_ID, EMP_LNAME, EMP_CNTRY, EMP_SAL, EMP_FNAME FROM EMPLOYEE_INFO
QUERY 10: HOW TO REPORT ALL EMPLOYEES WITH SALARY VALUE ABOVE 60K?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL >= 60000
QUERY 11: HOW TO REPORT ALL EMPLOYEES FROM INDIA OR CANADA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_CNTRY = ‘INDIA’ OR EMP_CNTRY = ‘CANADA’
QUERY 12: HOW TO REPORT ALL EMPLOYEES FROM INDIA OR CANADA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_CNTRY IN (‘INDIA’,’CANADA’)
QUERY 13: HOW TO REPORT ALL EMPLOYEES EXCEPT FROM INDIA OR CANADA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_CNTRY NOT IN (‘INDIA’,’CANADA’)
QUERY 14: HOW TO REPORT ALL EMPLOYEES WITH SALARY BETWEEN 30000 AND 60000?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL BETWEEN 30000 AND 60000
QUERY 15: HOW TO REPORT ALL EMPLOYEES WITH SALARY NOT BETWEEN 30000 AND 60000?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL NOT BETWEEN 30000 AND 60000
QUERY 16: HOW TO REPORT ALL EMPLOYEES WHOSE SALARY IS UNKNOWN?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL IS NULL
QUERY 17: HOW TO REPORT ALL EMPLOYEES WHOSE SALARY IS KNOWN?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL IS NOT NULL
QUERY 18: HOW TO REPORT ALL EMPLOYEES WHOSE FIRST NAME IS ‘AMIN’?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_FNAME = ‘AMIN’
QUERY 19: HOW TO REPORT ALL EMPLOYEES WHOSE FIRST NAME STARTS WITH LETTER ‘A’?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_FNAME LIKE ‘A%’
QUERY 20: HOW TO REPORT ALL EMPLOYEES WHOSE FIRST NAME DOES NOT END WITH ‘N’?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_FNAME NOT LIKE ‘%N’
QUERY 21: HOW TO REPORT ALL EMPLOYEES WHOSE FIRST NAME CONTAINS 4 CHARACTERS ?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_FNAME LIKE ‘____’ — SPECIFY 4 UNDERSCORES
QUERY 22: HOW TO REPORT ALL EMPLOYEES FROM CANADA, THEN EMPLOYEES FROM INDIA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_CNTRY = ‘CANADA’
UNION ALL
SELECT * FROM EMPLOYEE_INFO WHERE EMP_CNTRY = ‘INDIA’
QUERY 23 : HOW TO REPORT ALL EMPLOYEES WITH ASCENDING ORDER OF SALARIES?
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_SAL ASC
QUERY 24: HOW TO REPORT ALL EMPLOYEES WITH DESCENDING ORDER OF SALARIES?
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_SAL DESC
QUERY 25 : HOW TO REPORT TOP 3 EMPLOYEES WITH HIGHEST SALARY?
SELECT TOP 3 * FROM EMPLOYEE_INFO ORDER BY EMP_SAL DESC
QUERY 26 : HOW TO REPORT 3RD AND REMAINING EMPLOYEES?
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_ID ASC OFFSET 2 ROWS
QUERY 27 : HOW TO REPORT 3RD AND REMAINING 2 EMPLOYEES ?
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_ID ASC OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY
QUERY 28 – 30 : HOW TO REPORT EMPLOYEE FULL NAME?
SELECT EMP_ID, EMP_FNAME + EMP_LNAME AS FullName FROM EMPLOYEE_INFO
SELECT EMP_ID, EMP_FNAME + EMP_LNAME AS “Full Name” FROM EMPLOYEE_INFO
SELECT EMP_ID, EMP_FNAME + EMP_LNAME AS [Full Name] FROM EMPLOYEE_INFO