Sunday 4 December 2011

SQL - Assignments 3


Appendix-C

Table Format

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