Assignment No. 16

Table Creation & DML Commands

TYPE A: VERY SHORT ANSWER QUESTION

1.  Which command is used for creating tables?

2.  Which is a constraint? Name some constraint that you can apply to enhance database
      integrity.

3.  What is the role of UNIQUE constraint? How is PRIMARY KEY constraint different from
      UNIQUE constraint?

4.  What is Primary key? What is PRIMARY KEY constraint?

5.  What is NOT NULL constraint? What are DEFAULT constraints?

6.  When column’s value is skipped in an INSERT command, which value is inserted in the
      database?

7.  Can a column defined with NOTNULL constraint, be skipped in an INSERT command?

8.  How would you view the structure of table Dept?

9.  Table NewEmpl has same structure as that EMPL. Write a query to insert data from EMPL

table into NewEmpl, where salary is more than Rs 4000 and commission is greater than 500.

10. What is the error in following statement?

UPDATE EMPL;

TYPE B: SHORT ANSWER QUESTION

1.  How constraints ensure the validity of data? Explain various types of constraints with
      example.

2.  What is FOREIGN key? How do you define foreign key in your table?

3.  How is FOREIGN KEY different from PRIMARY KEY command?

4.  What are table constraints? What are column constraints? How are these two different?

5.  What is default value? How do you define it? What is default value for a column for which no
      value is defined?

6.  Differentiate between:

(i)        DROP TABLE & DROP DATADABASE

(ii)       DROP TABLE & DROP clause of ALTER TABLE.

7.  Consider the following table and answer the following-

Table: Empl

empno        ename               job            mgr         hiredate             sal           comm     deptno

8369     SMITH              CLERK            8902       1990-12-18         800.00         NULL          20

8499     Anya                SALESMAN     8839       1991-02-20        1600.00       300.00         30

8521     SETH               SALESMAN     8839       1991-02-22        1250.00       500.00         30

8566     MAHADEVAN  MANAGER       8844       1991-04-02        2985.00        NULL          20

8888     SCOTT             ANALYST        8566       1992-12-09        3000.00        NULL          20

8839     AMIR               MANAGER       8844       1991-11-18        5000.00        NULL          10

8844     Gates               PRESIDENT    NULL       1991-11-18        5000.00        NULL          10

….      ……….             ………              …..          ……….             …….           …..           ….

….      ……….             ………              …..          ……….             …….           …..           ….

a)  Update all Ename so that it contains the entire name in capital letters.

b)  Increase the salary of employee by 10% in Empl table.

c)  Give commission of Rs 500 to all employees who joined in year 1982 in Empl table

d)  Modify table Empl, add   another column called Grade of VARCHAR type size 1 into it.

e)  In the added column Grade, assign grade as follows.

if sal is in range   700-1500   Grade is 1

If sal is in range 1500-2200 Grade is 2
If sal is in range 2200-3000 Grade is 3
if sal is in range 3000- Grade is 4

f)   Display the details of employees who are working under the employee named AMIR.

g)  Modify the definition of column Grade. Increase its size to 2.

h)  Drop the table Empl.

8.  Given the following tables:

Orders (Ordno, Ord_date, ProdNo, Qty) Product (Prodno,Descp,Price)

Payment (OrdNo,Pment)

Write query statements for following transaction.

a)   Increase price of all products by 10 %.

b)   List the details of all orders. Whose payment is pending.

c)   Decrease price by 10% for all those products for which order were placed 10 months
       before.

d)   Write a query to delete all those records from table Orders whose payment has been
       made.

9.  Create the table Employee based on the following table instance Chart.

Column name     ID               FirstName              LastName                DeptID

Data Type            NUMBER   VARCHAR             VARCHAR              NUMBER

Length                8                25                          25                           8

10. Write the command for the following-

a)  Create table CUSTOMER as per following Table structure.

Column Name   CustID        CustName     CustAdd1      CustAdd2      CustPhone

Data Type          NUMBER    VARCHAR     VARCHAR    VARCHAR     VARCHAR

Length               7                 30                 20                 30                 10

b)    Add one column Email of data type VARCHAR and size 30 to table Customer.

c)  Add one more column CustIncomeGroup of data type VARCHAR(10).

d)  Insert few records with relevant information in the Customer table.

e)  Drop the column CustomerIncomeGroup from table Customer.

11. Create table Employee as per following Table structure.

Col. name          EmpID         EmpName       EmpAdd        EmpPhone    EmpSal       DeptID

Key type             Primary                                                                                           Foreign

Nulls /Unique                       NOT NULL

Fk Table                                                                                                                   Department

Fk Column                                                                                                               Dept_ID

Datatype            NUMBER    VARCHAR      VARCHAR     VARCHAR     NUMBER   VARCHAR

Length               6                 20                   30                 10                  9,2             2