CBSE Class 12 Computer Science SQL Worksheet Set A

DATABASE CONCEPTS

Question. What is RDBMS?
Answer : RDBMS stands for relational data base management system. It is a software package that managed a database.

Question. What is the function of a database management system?
Answer : It provides the users that much information that is required by them.

Question. What is data redundancy?
Answer : It is duplication of data in a database. It leads to the problems like wastage of space and data inconsistency.

Question. What are different levels of data abstraction?
Answer : External level, conceptual level, physical level.

Question. What do you mean by relational database?
Answer : In relational database, the data is organized in form of tables(rows and columns).

Question. What is relational algebra?
Answer : Relational algebra is used to perform specific operation on existing relation to provide desired result. Relationl algebra is procedural language.

Question. What does union operator do?
Answer : It produces third relation that contains tuples from both the operand relation.

Question. What is normalization?
Answer : It is concerned with the transformation of the conceptual schema (logical data structures) into a computer represent able form.

Question. What is meant by functional dependency?
Answer : It is a relationalship that exist between any two fields of tables.

Question. What do you understand by domain?
Answer : A domain is a pool of values from which the actual vales appearing in a given column are drawn.

(Two Marks Questions)

Question. What is data model?
Answer : A data model is a collection of conceptual tools for describing data, relationship, data semantic etc. There are generally three data models available: relational, network and hierarchical model.
(a) Relational Model: The relational model represent data and relationships among data by a collection of tables known as relations, each of which has a number of columns with a unique names.
(b) Network Model: The Network model represent data by collections of records and relational ship among the data are represented by links which can be viewed as pointers. The record in the database are organized as collection of arbitrary graph.
(c) Hierarchical Model : This model is similar to network model in the sense that data are relational ships among the data are represented by records and links respectively. In this model records are represented by tree.

Question. What is relation?
Answer : A relation is a table i.e. data is arranged in rows and columns. A relational has the following properties.
(a) In any column of table, all items are of same kind where as items in different columns may not be of same kind.
(b) For a row, each columns must have an atomic values. .
(c) All rows of a relation are distinct.

Question. Define the terms : Primary Key, Alternate Key, Candidate Key
Answer : (a) Primary Key:- A Primary Key is a set of one or more attribute that can uniquely identify tuples with in the relation.
(b) Alternate Key:- A Candidate key which is not the primary key is known as alternate key.
(c) Candidate Key:- All attribute combinations inside a relation that can serve as primary key are candidate keys as they are candidate

Question. What is data independence? How logical data independence is different from physical data independence?
Answer : The ability to modify a scheme definition in one level without affecting a scheme definition in the next higher level is called data independence.
Logical data independence is different from physical as in logical the conceptual scheme can be modified without causing any changes in the scheme followed at view levels where as in physical level could be modified without affecting the scheme followed at conceptual level.

Question. What are views? How they are useful?
Answer : A view is a virtual table that does not really exist in its own right but is instead derived form one or more underlying base tables. The view is a kind of table whose contents are taken upon other tables depending upon given query condition.
The usefulness of a views lies in the fact that they provide an excellent way to give people access to some but not all of the information in a table.

Question. Define First, second and third normal forms?
Answer : A relation R is in first normal form( 1NF) if and only if all underlying domains of the relation contains atomic vales only.
A relation is said to be in second normal form (2NF) if and only if it is in 1NF and every non key attribute is fully dependent of the primary key.
A relation R is said to be in third normal form (3NF) if and only if it is in 2NF and every non key attribute non transitively dependent upon the primary key.

Question. How many type of user works in database ?
Answer : Three type of user can work with data base.
1. End User -> This user is not a computer trained person but it uses the data base to retrieve information.
2. Application System Analyst.:-This user is concerned about all of the data base at logical level i.e. what all data constitute the database.
3. Physical storage system analyst-> This user is concerned with the physical implementation details of the database such as which storage device? Which storage technique should be used.

Question. What is foreign Key? What is its purpose?
Answer : A non key attribute, whose value are derived from the primary key of some other table, is known as foreign key in the current table. The table in which this non-key attribute i.e. foreign key attribute exists, is called a foreign table.

Question. Define the terms Tuple and Attribute
Answer : Tuples: The rows of tables (relations) are generally referred to as tuples.
Attribute: The columns of tables are generally referred to as attribute.

Question. What do you understand by the terms Cardinality and Degree of the table?
Answer : Degree: The number of attributes in a relation determines the degree of a relation. A relation having 3 attributes is said to be a relation of degree 3.
Cardinality: The number of rows in a relation is known as Cardinality.

STRUCTURED QUERY LANGUAGE

(Two Marks Questions)

Question. What are DDL and DML?
Answer : The DDL provides statements for the creation and deletion of tables and indexes.
The DML provides statements to enter, update , delete data and perform complex queries on these tables.

Question. What is the difference between Where and Having Clause ?
Answer : The having clause places the condition on group but where clause places the condition on individual rows

Question. What are the different Data types available in SQL.
Answer : Data Type Description
VARCHAR2(w) Variable length character having Max width is 2000 character
CHAR(w) Fixed length character Min Length 1 Ma Length 255
NUMBER(w.p) Number with precision w and scale p.
DATE Date value from Jan. 1, 4712 BC to DEC. 31,4712 AD.

Question. What do you understand by constraints?
Answer : Constraints are used to enforce rules at table level when ever row is inserted. , updated or deleted from table.
Constraints can be defined to one of the Two level.
Column Level. Reference to a single column. can be defined any type of integrity.
Table Level. References one or more columns and is defined separately from definition of the columns in the table.

Question. Write some features of SQL?
Answer : Recovery ad Concurrency:- Concurrency is concerned with the manner in which multiple user operate upon the Database.
Security: The Security can be maintained by view mechanism.
Integrity Constraints-> Integrity constraints are enforced by the system.

Question. Write the rules to name an objects?
Answer : • The maximum length must be 30 characters long.
• The Object name should not contain quotation mark.
• The name must start with letter.
• The use of $ and # is discouraged in the object name.
• A name must not be a reserved name.

Question. What are group Functions
Answer : The aggregate functions are group functions. They return result based on groups of rows. The group functions are AVG(), COUNT(), MAX(), MI N(), SUM()

Question. What are column alias?
Answer : In many cases heading table may not be descriptive and hence it difficult to understand. In such case we use columns alias It will change column heading with column alias.

(Six Marks Questions)

Question. Table : SchoolBus

(a) To show all information of students where capacity is more than the no of student in order of rtno.
(b) To show area_covered for buses covering more than 20 km., but charges less then 80000.
(c) To show transporter wise total no. of students traveling.
(d) To show rtno, area_covered and average cost per student for all routes where average cost per student is - charges/noofstudents.
(e) Add a new record with following data:
(11, “ Moti bagh”,35,32,10,” kisan tours “, 35000)
(f) Give the output considering the original relation as given:
(i) select sum(distance) from schoolbus where transporter= “ Yadav Co.”;
(ii) select min(noofstudents) from schoolbus;
(iii) select avg(charges) from schoolbus where transporter= “ Anand travels”;
(iv) select distinct transporter from schoolbus;
Answer :
(a) select * from schoolbus where capacity>noofstudents order by rtno;
(b) select area_covered from schoolbus where distance>20 and charges < 80000;
(c) select transporter, sum(noofstudents) from schoolbus group by transporter;
(d) select rtno, area_covered, charges/noofstudents avgcost from schoolbus;
(e) insert into schoolbus values(11,'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);
(f) Output of given following commands will be :
(i) select sum(distance) from schoolbus where transporter='Yadav Co.';
SUM(DISTANCE)
                  50
(ii) select min(noofstudents) from schoolbus;
MIN(NOOFSTUDENTS)
                  32
(iii) select avg(charges) from schoolbus where transporter='Anand travels';
AVG(CHARGES)
81666.6667
(iv) select distinct transporter from schoolbus;
TRANSPORTER
Anand travels
Bhalla Co.
Kisan Tours
Speed travels
Yadav Co.
shivamtravels
6 rows selected.

Question. Write SQL command for (i) to (vii) on the basis of the table SPORTS
Table: SPORTS

(b) Display the number of students getting grade ‘A’ in Cricket.
(c.) Display the names of the students who have same game for both Game1 and Game2.
(d) Display the games taken up by the students, whose name starts with ‘A’.
(e) Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade ‘A’ in both Game1 and Game2.
(f) Add a new column named ‘Marks’.
Answer :(a) select name from sports where grade='C' or grade2='C';
(b) select count(name) from sports where (grade='A' and Game1='Cricket') or (grade2='A' and Game2='Cricket');
(c) select name from sports where game1=game2;
(d) select game1, game2 from sports where name like 'A%';
(e) update sports set marks = 200 where grade in ('A','B') and grade2 in ('A','B');
(f) Alter table sports add marks int;

Q 1 Print all records from salespeople

Q 2 Print customer no. and name from customers

Q 3 Print salesman’s number & amt from orders

Q 4 How many salesman have got orders?

Q 5 Print all records with rating>300.

Q6 Print all records where city = London.

Q 7 Print all records with city = London and rating not equal to 200

Q 8 Select all records whose either city is not London or rating is not greater than 200.

Q 9 Where either city = London and rating>200 or city = rome and rating <100.

Q 10 Where city is either London or rome.

Q 11 Where Snum is 1001 or 1007, 1004.

Q 12 Where Cnum is between .10 and .12

Q 13 Where Q12 but exclusive .1 & .12

Q 14 Where Cname starts from P and end with G and lengthof 6 characters

Q 15 Where amount < 2000

Q 16 Count all records in costumer.

Q 17 Print maximum of amt + bal

Q 18 Find max amt of each salesperson in orders table.

Q 19 Find salesnumber and max amount of each salesnumber

Q 20 Find Snum, max (amt) of all Snum having max (amt) >3000

Q 21 Find sum & max amt of all Snum where date = 10/03/90

Q 22 Find Snum, max amt from orders for all Snum having Snum in (1002, 1007)

Q 23 Find total orders for each Snum

Q 24 Find all orders in descending order of date

Q 25 Find Snum, max amt of all Snum order by Snum

Q 26 List each order no followed by name of customers

Q 27 Give names of both the salesperson and customer for each order after the order no

Q 28 List all customer salesname, comm. Serviced by salespeople with comm. > 12%

Q 29 Calculate amount of salesperson comm. on each order by a customer with rating greater than 100

Q 30 Delete all records with orders 1/1/99

Q 31 Create view that contains Cnum,snum and sname

Q 32 Change the city Rome to Italy.

Q33 Drop all tables.

Q34 Print total order of a customer with cnum=’255’

Q35 Print all orders that are more than 5000/-