Download as:
Rating : ⭐⭐⭐⭐⭐
Price: $10.99
Language:EN
Pages: 19
Words: 1595

Set primary key using alter command

SELECT * FROM EMPLOYEE;

  1. Write a query to get only "FIRSTNAME" column from "EMPLOYEE" table

SELECT UPPER(FIRST_NAME) AS "First Name" FROM EMPLOYEE ;

  1. Write a query to get FIRST_NAME in lower case as "First Name".

 

SELECT FIRST_NAME +' '+ LAST_NAME AS [FULL NAME] FROM EMPLOYEE ;

  1. 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'
  1. 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 '%[%]%'

  1. Get the highest "SALARY" from EMPLOYEE table.

Query:

SELECT MAX(SALARY) FROM EMPLOYEE ;
  1. Show "JOINING_DATE" in "dd mmm yyyy" format, ex- "10 Feb 2014"

Query:

SELECT CONVERT(VARCHAR(20),JOINING_DATE,106) FROM EMPLOYEE ;
  1. 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

  1. 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
  1. get employee name, project name order by firstname from "EMPLOYEE" and "ASSIGNMENT" for those employee which have assigned project already.

  1. 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

Copyright © 2009-2023 UrgentHomework.com, All right reserved.