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)
);
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2023 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.