Following are the tables used in the examples and there records.

1) EMPLOYEE

Name Null? Type

------------------------------- -------- ----

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ---------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

2) DEPARTMENT

Name Null? Type

------------------------------- -------- ----

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

DEPTNO DNAME LOC

--------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

NOTE : The tables given above are the two basic tables used as an example throughout this book. In ORACLE these tables are present by the name of EMP and DEPT by default . Students are expected to create these tables in order to be familiar with the CREATE and INSERT statement.

SQL - Assignments 2

10. All Employee’s with the job as ‘CLERK’ are now called as Administrative Officers.

Update the Employee table for this.

11. Update salaries of all the SALESMAN , by increasing it by Rs.1000/-.

12. Increase the salary of KING by Rs3000/-.

13. Delete the employees who gets commission less then 100.

14. Find the SUM, AVERAGE, MINIMUM, MAXIMUM salary from the Employee table.

15. Count the number of employees in each department.

16. Count the number of items ordered by the customerid 10298. (Use items_ordered table).

17. List the Item name whose price is the highest .

18. List the sum of salary department wise.

19. Count the number of ‘CLERK’ in each department and their sum of salary.

20. List the department number who has more than 2 ANALYST.

21. Write a query using a join to determine which items were ordered by each of the customers in the customers table. Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.

22. List the name, department name, and employee number of all managers. Write a SQL query that will retrieve this information. Order the list in alphabetical order by department name.

23. List the employee name and department name of all employees that work in a department that has at least 3 employees. Write a SQL query to retrieve this information. Order the list in alphabetical order first by department name, then by employee name.

24. Write a SQL query that will return all the unique department numbers that are represented in the EMPLOYEE table, ordered by department number.

25. Write a SQL query to return the employee number, name and salary of all ‘CLERK’ who work in Department 10.

26. Write a SQL query to find employee number, name and job of all employees who are not managers.

27. Write a SQL query to find employee number, name and salary of all employees who get less than 1000 or more than 4000, ordered by salary.

28. Write a SQL query to find the name and employee number of all salesmen whose name begins with the letter S.

29. Use the IN operator to write a SQL query to find the name and employee number of all employees who are analysts or managers, ordered alphabetically by name.

30. Write a SQL query to find the employee number, name, salary and commission of all employees who have been paid commission and whose salary is greater than 2000.

31. Consider that the value in the Salary column is a monthly figure. Suppose that you are the owner of this company and you want to find out how much each employee would be paid per month if you increased his or her salary by 5%. Write a SQL query to get this information.

32. Again, consider that the value in the Salary column is a monthly figure. Suppose that you as the owner of this company want to compute how much each employee would be paid per month if you gave everyone a 3% pay cut. Write a SQL query to get this information.

Day3

33. Using subquery list the employee name and job of the employees whose job is same as ‘ALLEN’ job.

34. List all the details from the department table whose employees exists in the employee table. (use correlated subquery).

35. List the employee name , department number and salary of the employee whose salary is the maximum in his department.

36. List the names of the customers who have ordered items from us.

37. List the names of the employees from the employee table who are Project managers (also use the project table).

38. Using subquery list the employee name , job of the employees who do not belong to the department which in ‘CHICAGO’.

39. List the name of the project which has the highest number of persons in the project.

40. List the customerid, customer name whose order_date is the oldest one.

Day4

41. Write a program that declares and assigns values to the variables a,b,c, and then does the following :

Halves the values of a,

Doubles b,

Multiplies c by itself

42. Write a program that enables a user to input an integer. The program should then state whether the integer is evenly divisible by 5.

43. Ask the user to input two integers a, b and then determine whether or not a id divisible by b.

44. Your block should read in two real numbers and tell whether the product of the two numbers is equal to or greater than 100.

45. Write a program that inputs two numbers a,b, and thendetermines whether the product of the two numbers is greater than half of a.

46. Input a number and determine whether it is within a given range ( for example between 1 and 10). The low and high values of the range may be input by the user rather than fixed by the program.

47. Check if a given year is a leap year. The condition is year should be divisible by 4 and not divisible by 100 or divisible by 400.

48. See if a number is odd or even.

49. Check if a number is positive or negative.

50. Ask the user to enter the weight of an apple box. If the

Weight is >= 10kg, rate= Rs. 5 / Kg

Weight is <10Kg, rate= Rs.7 / Kg

Calculate the cost of the apple box.

51. Program should accept the age of the user. Depending upon the following conditions it should output:-

Age < 18 years, “child”

Age >= 18 years and <21 years “major”

Age>=21 years “adult”

52. Write a program to determine if one character string exists inside another character string.

53. Suppose the grade obtained by a student depends upon his scores and the grading rules is as follows:

Scores Grades

95-100 A

85-94 B

70-84 C

60-69 D

0-59 E

Write a block to accept a student’s marks and accordingly output his grade.

54. Write a program containing a loop that counts from 1 to 1000 using a variable I, which is incremented each time around the loop. The program should output the value of I every hundred iterations (i.e. the output should be 100, 200, etc).

55. Write a procedure which accepts the department number as an argument and updates the salary of the employees belonging to that department by 1000.

56. Write a procedure that accepts employee number as a parameter and displays the department number and salary.

57. Write a procedure to accept the employee number and display the grade of the employee using the salgrade table.

58. Write a procedure which deletes the duplicate rows in a table whose name is accepted as an argument from the user.

59. Write a function which excepts employee number as the argument and returns the hiredate for that employee.

SQL - Assignments 1

1.Create table Project which has the following fields :

PNO number(3)

PNAME varchar2(60)

PMGR number(4)

PERSONS number(5)

BUDGET number(8,2)

PSTART date

PEND date

Also enforce the following constraints on the fields: PNO should be the primary key with the constraint name as prj_pk, PNAME should have the unique constraint,foreign key constraint on PMGR reference to the empno in the employee table, BUDGET should have the not null constraint, check constraint to check that the PSTART date is less than the PEND date.

ANS:

CREATE TABLE PROJECT(PNO NUMBER(3) CONSTRAINT PRJ_PK PRIMARY KEY,PNAME VARCHAR2(60) CONSTRAINT PNAME_CNSTRNT UNIQUE KEY,PMGR NUMBER(4) CONSTRAINT PMGR_CNSTRNT REFERENCES FOREIGN KEY(EMPLOYEE.EMPNO),PERSONS NUMBER(5),BUDGET NUMBER(8,2) NOT NULL,PEND DATE,PSTRAT DATE CONSTRAINT BUD_CNSTRNT CHECK CHECK PSTART<PEND);

2. Create table customer which has the following fields:

Customerid number(5)

Firstname varchar2(10)

Lastname varchar2(20)

City varchar2(10)

ANS:

CREATE TABLE CUSTOMER(CUSTOMERID NUMBER(5),FIRSTNAME VARCHAR2(10),LASTNAME VARCHAR2(20),CITY VARCHAR2(10));

3. Create table items_ordered which has the following fields:

Customerid number(5)

Order-Date date

Item varchar2(20)

Quantity number(4)

Price number(4,2)

ANS:

CREATE TABLE ITEMS_ORDERED(CUSTOMERID NUMBER(5),ORDER-DATE DATE,ITEM VARCHAR2(20),QUANTITY NUMBER(4),PRICE NUMBER(4,2));

4. Alter table customer add the column State varchar2(20) to it.

ANS:

ALTER TABLE CUSTOMER ADD COLUMN STATE VARCHAR2(20);

5. Alter table add primary key constraint to the Customerid of the customer table. Alter the

same table and modify the Firstname column and change varchar2(10) to varchar2(20).

ANS:

ALTER TABLE CUSTOMER MODIFY CUSTOMERID NUMBER(5) CONSTRAINT CID_CNSTRNT FOREIGN KEY;

ALTER TABLE CUSTOMER MODIFY FIRSTNAME VARCHAR2(20);

1. Alter table items_ordered and add the foreign key constraint to the Customerid .

2. Using the insert statement enter the following records in the PROJECT table.

Pno	Pname	Pmgr	Persons	Budget	Pstart	Pend
100	Java	7499	5	5000	23- Dec-90	21-Feb-91
101	ChannelPoint	7698	10	4000	20-Dec-90	03-Apr-91
102	CRM	7788	12	8000	19-Jun-91	20-Aug-92
103	GE	7876	30	9000	23-Mar-95	15-Dec-95
104	GE Lighting	7900	20	5000	16-Sep-93	20-Oct-94
105	E-com	7934	40	9000	09-Mar-98	28-Dec-99

ANS:

INSERT INTO PROJECT VALUES(100,’JAVA’,7499,5,5000,’23-DEC-90’,’21-FEB-91’);

INSERT INTO PROJECT VALUES(101,’CHANNELPOINT’,7698,10,4000,’20-DEC-90’,’03-FEB-91’);

INSERT INTO PROJECT VALUES(102,’CRM’,7788,12,4000,’20-DEC-90’,’03-FEB-91’);

8. Insert the following records in the Customer table.

customerid	firstname	Lastname	City	state

10101	John	Gray	Lynden	Washington
10298	Leroy	Brown	Pinetop	Arizona
10299	Elroy	Keller	Snoqualmie	Washington
10315	Lisa	Jones	Oshkosh	Wisconsin
10325	Ginger	Schultz	Pocatello	Idaho
10329	Kelly	Mendoza	Kailua	Hawaii
10330	Shawn	Dalton	Cannon Beach	Oregon
10338	Michael	Howell	Tillamook	Oregon
10339	Anthony	Sanchez	Winslow	Arizona

9. Insert the following records in the Items_Ordered table.

customerid	order_date	Item	quantity	price

10330	30-Jun-1999	Pogo stick	1	28.00
10101	30-Jun-1999	Raft	1	58.00
10298	01-Jul-1999	Skateboard	1	33.00
10101	01-Jul-1999	Life Vest	4	125.00
10299	06-Jul-1999	Parachute	1	1250.00
10339	27-Jul-1999	Umbrella	1	4.50
10101	18-Aug-1999	Rain Coat	1	18.30
10298	19-Sep-1999	Lantern	2	29.00
10298	01-Dec-1999	Helmet	1	22.00
10101	30-Dec-1999	Hoola Hoop	3	14.75
10330	01-Jan-2000	Flashlight	4	28.00
10101	02-Jan-2000	Lantern	1	16.00
10299	18-Jan-2000	Inflatable Mattress	1	38.00
10101	08-Mar-2000	Sleeping Bag	2	88.70
10298	18-Mar-2000	Pocket Knife	1	22.38

saravanan velu

Sunday, 4 December 2011

SQL - Assignments 3

Appendix-C

Table Format

SQL - Assignments 2

SQL - Assignments 1