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
