Write any FOUR questions (each question carries equal marks)
1. Write the general format and usages of the following SQL commands.
(a) Create table, (b) Alter table (c) Drop table.
2. Consider the following relational schemes:
EMPLOYEE (EMPLOYEE_NAME, STREET, CITY)
WORKS (EMPLOYEE_NAME, COMPANYNAME, SALARY)
COMPANY (COMPANY_NAME, CITY).
a) Specify the table definitions of the given schemes in SQL
b) Insert two records in to each table.
3. Consider the relations schemes as given below.
EMP (ENO, ENAME, AGE, BASIC_SALARY)
WORK_IN (ENO, DNO) and DEPT (DNO, DNAME, CITY)
Express the following queries in SQL
(i) Find names of employees who work in a deptt. in “Delhi”.
(ii) Find name of employee who earns highest salary in ‘SALES’ department.
4. Define a table in SQL called Client, which is used to store information about the clients. Define CLIENT_NO as the primary key whose first letter must start with ‘C’. The column ‘NAME’ should not allow NULL values.
Column name Data type Size
CLIENT_NO Varchar2 6
NAME Varchar2 20
ADDRESS Varchar2 30
CITY Varchar2 15
STATE Varchar2 15
PINCODE Number 6
BAL_DUE Number 10, 2
5. Explain the function of each of the clauses in the SELECT statement. (a) FROM (b) ORDER BY (c) WHERE (d) GROUP BY(e) HAVING
6. Explain each of the following SQL commands with suitable examples.
(a) ALTER – Both ADD and MODIFY
(b) SELECT – With GROUP BY and HAVING clauses
(c) INSERT – Assuming a table with at least five types of attributes.