MASTER OF COMPUTER APPLICATIONS
Course Code : MCS-023
Course Title : Introduction to Database Management Systems
Assignment Number : MCA(II)/023/Assignment/15-16
Maximum Marks : 100
Weightage : 25%
Consider the following EMP table:
ENAME DEPT-NAME DESIGNATION SALARY DATE-OF-JOIN
KARAN ACCOUNTING DIRECTOR 50000 Nov 17, 2012
FARAH RESEARCH ANALYST 30000 Dec 03, 1991
SCINDIA RESEARCH ANALYST 30000 Dec 09, 2002
JOY RESEARCH MANAGER 29750 Apr 02, 2011
BHASKAR SALES MANAGER 28500 May 01, 1999
CHANDER ACCOUNTING MANAGER 24500 Jun 09, 2000
ANIL SALES SALESMAN 16000 Feb 20, 1991
TOMAR SALES SALESMAN 15000 Sep 08, 2001
MILIND ACCOUNTING CLERK 13000 Jan 23, 2002
SAXENA SALES SALESMAN 12500 Sep 28, 1999
TOMAR SALES SALESMAN 14500 Feb 22, 1997
ANAND RESEARCH CLERK 11000 Jan 12, 1993
GEORGE SALES CLERK 9500 Dec 03, 1990
SURESH RESEARCH CLERK 8000 Dec 17, 1992
We have to create Table in access:
QUERY for Creating EMP table:
CREATE TABLE S (S_No CHAR (5) NOT NULL, SNAME CHAR (20) NOT NULL, STATUS SMALLINT NOT NULL, CITY CHAR (15) NOT NULL, PRIMARY KEY (S_No));
Answer the following queries in SQL.
1. QUERY for Find all the ENAME’s whose salary is < Rs.20000.
SELECT * FROM EMP WHERE SALARY < 20000;
2. QUERY for Find all the employees working with SALES Department and with designation MANAGER.
SELECT * FROM EMP WHERE DEPT_NAME=’SALES’ AND DESIGNATION=’MANAGER’;
3. QUERY for Find all employees whose name starts with S.
SELECT * FROM EMP WHERE ENAME LIKE “S*”;
4. QUERY for Find total number of employees who work with RESEARCH department.
SELECT COUNT (*) FROM (SELECT * FROM EMP WHERE DEPT_NAME=’RESEARCH’);
5. QUERY for Find all the employees who joined after Jan 1, 2010.
SELECT * FROM EMP WHERE DATE_OF_JOIN > #01/01/2010#;
6. QUERY for Count number of employees whose salary is between Rs.8000 and Rs.12500.
SELECT * FROM EMP WHERE SALARY BETWEEN 8000 AND 12500;
7. QUERY for Sort the supplier table by ENAME.
SELECT * FROM EMP ORDER BY ENAME;
8. QUERY for Find the employees whose designation is SALESMAN and joined after 1st Aug, 1990.
SELECT * FROM EMP WHERE DESIGNATION=’SALESMAN’ AND DATE_OF_JOIN > #01/08/1990#;
9. QUERY for Find all the employees whose designation is CLERK.
SELECT * FROM EMP WHERE DESIGNATION=’CLERK’;
10. QUERY for Count number of SALESMAN in SALES department.
SELECT COUNT (*) FROM EMP WHERE DESIGNATION=’SALESMAN’ AND DEPT_NAME=’SALES’;
11. QUERY for Count all the number of employees who are working with the company.
SELECT COUNT (*) FROM EMP;
12. QUERY for Find S# of supplier who supply part “p2”.
WRONG QUESTION
13. QUERY for Find the employees joined between 1st Jan, 1997 and 31st Dec, 2010.
SELECT * FROM EMP WHERE DATE_OF_JOIN BETWEEN #01/01/1997# AND #31/12/2010#;
14. QUERY for Sort the table by the SALARY, descending order.
SELECT * FROM EMP ORDER BY SALARY DESC;
15. QUERY for Find the employees with similar names and display their designation, department and data of join.
SELECT DESIGNATION, DEPT_NAME, DATE_OF_JOIN FROM EMP WHERE (ENAME) IN (SELECT ENAME FROM EMP GROUP BY ENAME HAVING COUNT (*) >1);
