Set primary key using alter command
SELECT * FROM EMPLOYEE; |
---|
Write a query to get only "FIRSTNAME" column from "EMPLOYEE" table
SELECT UPPER(FIRST_NAME) AS "First Name" FROM EMPLOYEE ; |
---|
Write a query to get FIRST_NAME in lower case as "First Name".
SELECT FIRST_NAME +' '+ LAST_NAME AS [FULL NAME] FROM EMPLOYEE ; |
---|
Get all employee detail from EMPLOYEE table whose " FIRST_NAME" start with latter 'D'.
Query:
SELECT * FROM EMPLOYEE WHERE FIRST_NAME like 'D%' |
---|
SELECT * FROM EMPLOYEE WHERE FIRST_NAME like '%W' |
---|
Get all employee detail from EMPLOYEE table whose " FIRST_NAME" start with any single character between 'A-N'
Query:
CREATE EMPLOYEE TABLE
EMP_ID | FIRST_NAME | LAST_NAME | GENDER | DEPARTMENT | JOINING_DATE | SALARY |
---|---|---|---|---|---|---|
1001 | ANDREW | CENCINI | MALE | IT | 08-01-2014 | 300000 |
1002 | STEVEN | THROPE | MALE | HR | 10-02-2014 | 400000 |
1003 | ANNE | HELLUNG-LARSEN | FEMALE | FINANCE | 20-03-2014 | 500000 |
1004 | DENE | SENA | FEMALE | ACCOUNT | 30-04-2014 | 200000 |
Query:
SELECT * FROM EMPLOYEE WHERE Gender like '__le' --there are two "_" |
---|
Query:
SELECT * FROM EMPLOYEE WHERE FIRST_NAME like '%[%]%' |
---|
Get the highest "SALARY" from EMPLOYEE table.
Query:
SELECT MAX(SALARY) FROM EMPLOYEE ; |
---|
Show "JOINING_DATE" in "dd mmm yyyy" format, ex- "10 Feb 2014"
Query:
SELECT CONVERT(VARCHAR(20),JOINING_DATE,106) FROM EMPLOYEE ; |
---|
Show only time part of the "JOINING_DATE".
Query:
SELECT CONVERT(VARCHAR(20),JOINING_DATE,108) FROM EMPLOYEE ; |
---|
EMPLOYEE TABLE
ASSIGNMENT TABLE
ASSIGN_ID | EMP_ID | ASSIGNMENT_NAME | ASSIGN_DATE | DUE_DATE |
---|---|---|---|---|
11 | 1001 | MENAGEMENT | 01-02-2014 | 01-03-2014 |
22 | 1001 | HR SYSTEM | 11-02-2014 | 11-04-2014 |
33 | 1002 | MENAGEMENT | 01-03-2014 | 01-04-2014 |
44 | 1003 | FINANCE | 01-03-2014 | 01-04-2014 |
55 | 1003 | DBMS | 11-04-2014 | 11-05-2014 |
66 | 1004 | DB | 10-04-2014 | 10-06-2014 |
GROUP BY DEPARTMENT ORDER BY SUM(SALARY) ASC |
---|
query to get the department wise average salary from EMPLOYEE table order by salary ascending.
SELECT DEPARTMENT, MAX(SALARY) AS TOTAL FROM EMPLOYEE GROUP BY DEPARTMENT |
---|
HAVING COUNT(*)> 1 |
---|
ASSIGN_ID | EMP_ID | ASSIGNMENT_NAME | ASSIGN_DATE | DUE_DATE |
---|---|---|---|---|
11 | 1001 | MENAGEMENT | 01-02-2014 | 01-03-2014 |
22 | 1001 | HR SYSTEM | 11-02-2014 | 11-04-2014 |
33 | 1002 | MENAGEMENT | 01-03-2014 | 01-04-2014 |
44 | 1003 | FINANCE | 01-03-2014 | 01-04-2014 |
55 | 1003 | DBMS | 11-04-2014 | 11-05-2014 |
66 | 1004 | DB | 10-04-2014 | 10-06-2014 |
get employee name, project name order by firstname from "EMPLOYEE" and "ASSIGNMENT" for those employee which have assigned project already.
order by first_name "EMPLOYEE" and "ASSIGNMENT" for all employee even they have not assigned assignment.
SELECT FIRST_NAME, ASSIGNMENT_NAME FROM EMPLOYEE A LEFT JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME |
---|
SELECT FIRSTNAME,ASSIGNMENT_NAME FROM EMPLOYEE A FULL OUTER JOIN ASSIGNMENT B ON A.EMP_ID=B.ASSIGN_ID ORDER BY FIRST_NAME |
---|
5. JOIN related SQL Query -
EMP_ID | FIRST_NAME | LAST_NAME | GENDER | DEPARTMENT | JOINING_DATE | SALARY |
---|---|---|---|---|---|---|
1001 | ANDREW | CENCINI | MALE | IT | 08-01-2014 | 300000 |
1002 | STEVEN | THROPE | MALE | HR | 10-02-2014 | 400000 |
1003 | ANNE | HELLUNG-LARSEN | FEMALE | FINANCE | 20-03-2014 | 500000 |
1004 | DENE | SENA | FEMALE | ACCOUNT | 30-04-2014 | 200000 |
NULL, [LAST_NAME] NVARCHAR(50) NULL, [SALARY] DECIMAL(10,2) NULL, [JOINING_DATE] DATETIME NULL, [DEPARTMENT NVARCHAR(20) NULL, [GENDER] VARCHAR(10) NULL ) |
---|
4. set primary key using alter command
6. Small Tricky SQL Queries -
OUTPUT : 0
OUTPUT : 1
OUTPUT : ANNE1
OUTPUT : ANNE
OUTPUT : 7