1. Explain the advantage of database system over file processing system.
2. Explain what is mean by the following terms in DBMS, illustrate each one with an example.
a. Foreign key b. Primary key c. weak entity set d. Candidate key
• create the following check constraints
Data values being inserted into the column client_no must start with ‘c’.
Data values being inserted into the column name should be allowed in upper case only?
4. Q-1 Find list of emp whose empno is greater then manager no ?
Q-2 Find all manager not in dept no 10 ?
Q-3 Display everyone in dept no 20 who is neither a clerk, salesman nor analyst ?
Q-4 List all emp names, their jobs, salary in ascending order by theire jobs ?
Q-5 List all empname who are ordered by the jobs within each job there should be Descending order by empno ?
Q-6 Retrieve details of emp whose sal is between 1000 and 2000 both inclusive?
Q-7 Retrieve all details of employees whose name is either smith, blake, allen, scott, clark and King ?
Q-8 To list all employees whose salary is greater than or equal to 2000.
Q-9To list all the employees hired during the year 1982
Q-10To get all employees from dept 10 and 20.
Q-11To list all employees whose name begins with ‘J’.
Q-12To list employees whose name begins with ‘J’ and has ‘N’ as the third character?
Q-13To list all employees not entitled for commission.
Q-14To list all employees who receive commission.
Q-15To list employees working in dept no 10 or 20.
Q-16To list employees hired after 01-jan-81 and working in dept no. 10
Q-17To list employees working in dept other than 10.
5. Construct ER Model for Banking Enterprise?
6. Write a query in sql to create a table employee and department.
Employee(empno,ename,deptno,job,hiredate)
Dept(deptno,dname,loc)
Include the following constraints on column of emp table.
a) to make the empno as primary key of the table and
b) to ensure that the ename column does not contain NULL values and
c) the job column to have only UPPERCASE entries and
d) to put the current date as default date in hire date column in case data is not supplied for the column.
Include the following constraints on column of dept table.
a) to make deptno as primary key.
b) to ensure dname,loc coloumns does not contain NULL values
Also enforce REFERENTIAL INTEGRITY, declare deptno field of dept table as primary key and deptno field of emp table as foreign key.
2. Explain what is mean by the following terms in DBMS, illustrate each one with an example.
a. Foreign key b. Primary key c. weak entity set d. Candidate key
3. Create a table client
master with the following fields client_no, name, address1, address2, city, state, pin_code, remarks, balance due and add the constraints?
• Create a primary
key constraint on the column client_no.?• create the following check constraints
Data values being inserted into the column client_no must start with ‘c’.
Data values being inserted into the column name should be allowed in upper case only?
4. Q-1 Find list of emp whose empno is greater then manager no ?
Q-2 Find all manager not in dept no 10 ?
Q-3 Display everyone in dept no 20 who is neither a clerk, salesman nor analyst ?
Q-4 List all emp names, their jobs, salary in ascending order by theire jobs ?
Q-5 List all empname who are ordered by the jobs within each job there should be Descending order by empno ?
Q-6 Retrieve details of emp whose sal is between 1000 and 2000 both inclusive?
Q-7 Retrieve all details of employees whose name is either smith, blake, allen, scott, clark and King ?
Q-8 To list all employees whose salary is greater than or equal to 2000.
Q-9To list all the employees hired during the year 1982
Q-10To get all employees from dept 10 and 20.
Q-11To list all employees whose name begins with ‘J’.
Q-12To list employees whose name begins with ‘J’ and has ‘N’ as the third character?
Q-13To list all employees not entitled for commission.
Q-14To list all employees who receive commission.
Q-15To list employees working in dept no 10 or 20.
Q-16To list employees hired after 01-jan-81 and working in dept no. 10
Q-17To list employees working in dept other than 10.
5. Construct ER Model for Banking Enterprise?
6. Write a query in sql to create a table employee and department.
Employee(empno,ename,deptno,job,hiredate)
Dept(deptno,dname,loc)
Include the following constraints on column of emp table.
a) to make the empno as primary key of the table and
b) to ensure that the ename column does not contain NULL values and
c) the job column to have only UPPERCASE entries and
d) to put the current date as default date in hire date column in case data is not supplied for the column.
Include the following constraints on column of dept table.
a) to make deptno as primary key.
b) to ensure dname,loc coloumns does not contain NULL values
Also enforce REFERENTIAL INTEGRITY, declare deptno field of dept table as primary key and deptno field of emp table as foreign key.
7. Convert the following ER Model into Table and Write the SQL Queries.
8. What is a Database and DBMS? Explain different types of Cardinality?
