SQL: Book Back Questions and Answers

Computer Science : Database concepts and MySql : Structured Query Language(SQL)

Evaluation

Part – I

Choose the best answer (1 Marks)

1.Which commands provide definitions for creating table structure, deleting relations, and modifying relation schemas.

a. DDL

b. DML

c. DCL

d. DQL

2.Which command lets to change the structure of the table?

a. SELECT

b. ORDER BY

c. MODIFY

d. ALTER

3.The command to delete a table is

A) DROP

B) DELETE

C) DELETE ALL

D) ALTER TABLE

4.Queries can be generated using

a. SELECT

b. ORDER BY

c. MODIFY

d. ALTER

5.The clause used to sort data in a database

a. SORT BY

b. ORDER BY

c. GROUP BY

d. SELECT

Part -II

Answer the following questions       (2 Marks)

1. Write a query that selects all students whose age is less than 18 in order wise.

Ans. SELECT ALL Age FROM student WHERE Age < 18;

2. Differentiate Unique and Primary Key constraint.

Ans.

3. Write the difference between table constraint and column constraint?

Ans.

Column constraint :  Column constraint can be applied only to individual column

Table constraint   : Table constraint is applied to a group of fields of the table.

4. Which component of SQL lets insert values in tables and which lets to create a table?

Ans. Component of SQL :

(i) Insert values of tables

DDL - Data Definition Language

(ii) Create a table

DML - Data Manipulation Language

5. What is the difference between SQL and MySQL?

Ans. The main difference between SQL and MySQL is that the SQL is a database language to manage data in a relational database while MySQL is an open source Relational Database Management System that helps to manage relational databases. 

Part –III

Answer the following questions       (3 Marks)

1. What is a constraint? Write short note on Primary key constraint.

Ans. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. Constraint is a condition applicable on a field or set of fields.

Primary Constraint:

(i) This constraint declares a field as a Primary key which helps to uniquely identify a record.

(ii) It is similar to unique constraint except that only one field of a table can be set as primary key.

(iii) The primary key does not allow NULL values and therefore a field declared as primary key must have the NOT NULL constraint.

2. Write a SQL statement to modify the student table structure by adding a new field.

Ans. ALTER TABLE <table-name> ADD <column- name><data type><size>;

3. Write any three DDL commands.

Ans. DELETE, TRUNCATE AND DROP

statement:

(i) DELETE : The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

(ii) TRUNCATE : The TRUNCATE command is used to delete all the rows, the structure remains in the table and free the space containing the table.

(iii) DROP : The DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.

4. Write the use of Savepoint command with an example.

Ans. (i) The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required.

(ii) The different states of our table can be saved at anytime using different names and the rollback to that state can be done using the ROLLBACK command.

(iii) Example :

SAVEPOINT savepoint_name;

UPDATE Student SET Name = ‘Mini’ WHERE Admno= 105;

SAVEPOINT A;

INSERT INTO Student VALUES(106, 'Jisha', 'F', 19, 'Delhi');

SAVEPOINT B;

5. Write a SQL statement using DISTINCT keyword.

Ans. (i) The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data.

(ii) For Example: SELECT DISTINCT Place FROM Student;

Part –IV

Answer the following questions       (5 Marks)

1. Write the different types of constraints and their functions.

Ans. Type of Constraints : Constraints ensure database integrity, therefore known as database integrity constraints. The different type of constraints are:

Unique Constraint

(i) This constraint ensures that no two rows have the same value in the specified columns. For example UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:

(ii) CREATE TABLE Student:

(

Admno integer NOT NULL UNIQUE, → Unique constraint

Name char (20) NOT NULL,

Gender char (1),

Age integer,

Place char (10),

);

(iii) The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL.

(iv) When two constraints are applied on a single field, it is known as multiple constraints. In the above Multiple constraints NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and at the end of the field definition a comma(,) is added. By adding these two constraints the field Admno must take some value ie. will not be NULL and should not be duplicated.

Primary Key Constraint:

(i) This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key.

(ii) The primary key does not allow NULL values and therefore a field declared as primary key must have the NOT NULL constraint.

(iii) Example showing Primary Key Constraint in the student table:

(iv) CREATE TABLE Student:

(

Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint

Name char(20) NOT NULL,

Gender char(l),

Age integer,

Place char(10),

);

(v) In the above example the Admno field has been set as primary key and therefore will help us to uniquely identify a record, it is also set NOT NULL, therefore this field value cannot be empty.

Default Constraint:

(i) The DEFAULT constraint is used to assign a default value for the field. When no value is given for the specified field having DEFAULT constraint, automatically the default value will be assigned to the field.

(ii) Example showing DEFAULT Constraint in the student table:

(iii) CREATE TABLE Student:

(

Admno integer NOT NULL PRIMARY KEY,

Name char(20)NOT NULL,

Gender char(l),

Age integer DEFAULT = “17”, → Default Constraint

Place char(10),

);

(iv) In the above example the “Age” field is assigned a default value of 17, therefore when no value is entered in age by the user, it automatically assigns 17 to Age.

Check Constraint:

(i) This constraint helps to set a limit value placed for a field. When we define a check constraint on a single column, it allows only the restricted values on that field. Example showing check constraint in the student table:

(ii) CREATE TABLE Student:

(

Admno integer NOT NULL PRIMARY KEY

Name char(20)NOT NULL,

Gender char(l),

Age integer (CHECK<=19), → Check

Constraint

Place char(10),

);

(iii) In the above example the check constraint is set to Age field where the value of Age must be less than or equal to 19.

(iv) The check constraint may use relational and logical operators for condition.

Table Constraint:

(i) When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition.

(ii) Let us take a new table namely Student 1 with the following fields Admno, Firstname, Lastname, Gender, Age, Place:

(iii) CREATE TABLE Student 1 :

.(

Admno integer NOT NULL,

Firstname char(20),

Lastname char(20),

Gender char(l),

Age integer,

Place char(10),

PRIMARY KEY (Firstname, Lastname) → Table constraint

);

(iv) In. the above example, the two fields, Firstname and Lastname are defined as Primary key which is a Table constraint.

2. Consider the following employee table. Write SQL commands for the qtns.(i) to (v).

(i) To display the details of all employees in descending order of pay.

(ii) To display all employees whose allowance is between 5000 and 7000.

(iii) To remove the employees who are mechanic.

(iv) To add a new row.

(v) To display the details of all employees who are operators.

Ans. (i) SELECT * FROM Employee ORDER BY PAY DESC;

(ii) SELECT * FROM Employee WHERE ALLOWANCE BETWEEN 5000 AND 7000;

(iii) DELETE FROM Employee WHERE DESIG='Mechanic';

(iv) INSERT INTO Employee VALUES ('c 1005', 'Abhi', 'Mechanic', 15000, 6500);

(v) SELECT * FROM Employee WHERE DESIG='Operator';

3. What are the components of SQL? Write the commands in each.

Ans. Components of SQL : SQL commands are divided into five categories:

 DDL - Data Definition Language

DML - Data Manipulation Language j

DCL - Data Control Language  

TCL - Transaction Control Language

DQL - Data Query Language

Data Definition Language :

(i) The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema.

(ii) It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.

(iii) The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.

(iv) ADDL performs the following functions :

■ It should identify the type of data division such as data item, segment, record and database file.

■ It gives a unique name to each data item type, record type, file type and data base.

■ It should specify the proper data type.

■ It should define the size of the data item.

■ It may define the range of values that a data item may use.

■ It may specify privacy locks for preventing unauthorized data entry.

(v) SQL commands which comes under Data Definition Language are:

Create: To create tables in the database.

Alter: Alters the structure of the database.

Drop: Delete tables from database.

Truncate: Remove all records from a table, also release the space occupied by those records.

Data Manipulation Language :

(i) A Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database.

(ii) In SQL, the data manipulation language comprises the SQL-data change statements, which modify stored data but not the schema of the database table.

(iii) After the database schema has been specified and the database has been created, the data can be manipulated using a set of procedures which are expressed by DML.

(iv) The DML is basically of two types :

* Procedural DML - Requires a user to specify what data is needed and how to get it.

* Non-Procedural DML - Requires a user to specify what data is needed without specifying how to get it.

(v) SQL commands which comes under Data Manipulation Language are:

Insert  : Inserts data into a table

Update : Updates the existing data within a table.

Delete : Deletes all records from a table, but not the space occupied by them.

Data Control Language:

(i) A Data Control Language (DCL) is a programming language used to control the access of data stored in a database. It is used for controlling privileges in the database (Authorization).

(ii) The privileges are required for performing all the database operations such as creating sequences, views of tables etc.

(iii) SQL commands which come under Data Control Language are:

Grant : Grants permission to one or more users to perform specific tasks.

Revoke : Withdraws the access permission given by the GRANT statement.

Transactional Control Language:

(i) Transactional control language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements.

(ii)  SQL command which come under Transfer Control Language are:

Commit : Saves any transaction into the database permanently.

Roll back : Restores the database to last commit state.

Save point : Temporarily save a transaction so that you can rollback.

Data query language :

(i) The Data Query Language consist of commands used to query or retrieve data from a database.

(ii) One such SQL command in Data Query Language is

Select : It displays the records from the table.

4. Construct the following SQL statements in the student table-

(i) SELECT statement using GROUP BY clause.

(ii) SELECT statement using ORDER BY clause.

Ans. (i) GROUP BY clause:

■ The GROUP BY clause is used with the SELECT statement to group the students on rows or columns having identical values or divide the table in to groups.

■ For example to know the number of male students or female students of a class, the GROUP BY clause may be used. It is mostly used in conjunction with aggregate functions to produce summary reports from the database.

■ The syntax for the GROUP BY clause is

■ SELECT <column-names> FROM ctable- name> GROUP BY <column-name>HAV- ING condition];

■ To apply the above command on the stu-dent table:

■  SELECT Gender FROM Student GROUP BY Gender;

■ The following command will give the below given result:

■ The point to be noted is that only two results have been returned. This is because we only have two gender types ‘Male’ and ‘Female’. The GROUP BY clause grouped all the ‘M’ students together and returned only a single row for it. It did the same with the ‘F’ students.

■ For example to count the number of male and female students in the student table, the following command is given :

■ SELECT Gender, count(*) FROM Student GROUP BY Gender;

■ The GROUP BY applies the aggregate functions independently to a series of groups that are defined by having a field value in common. The output of the above SELECT statement gives a count of the number of Male and Female students.

(ii) ORDER BY clause :

■  The ORDER BY clause in SQL is used to sort the data in either ascending or descending based on one or more columns.

(a) By default ORDER BY sorts the data in ascending order.

(b) We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

■ The ORDER BY clause is used as :

■ SELECT<column-name>[,<column- name>,....] FROM <table-name>ORDER BY

<columnl>,<column2>,...ASC| DESC;

■ For example:

To display the students in alphabetical order of their names, the command is used as

■ SELECT * FROM Student ORDER BY Name;

The above student table is arranged as follows:

5. Write a SQL statement to create a table for employee having any five fields and create a table constraint for the employee table.

Ans. CREATE TABLE employee

 (

empcode integer NOTNULL,

efirstname char(20),

elastname char(20),

Designation char(20),

Pay integer,

PRIMARY KEY (efirstname, elastname)

);