Unit 3: Data Management (DM-2)

Database Management& SQL

Value Points

1. A database (DB) is a collection of data describing the activities of one or more related
organization.
2. A Database Management System (DBMS) is software designed to assist in
maintaining & utilizing large collection of data.
Example:
Oracle, Microsoft, Sybase, MySQL,PostgreSQL, SQLite
3. SQL(Structured Query language) is a non procedural language used for querying
upon relational data bases(eg- Oracle , Sybase, SQL server , MYSQL etc).
4. DDL(Data definition language) is a part of SQL that facilitates defining/creating and
modifying the structure of table or view.
5. DML(Data manipulation language) is a part of SQL that facilitates insertion /
deletion/modification of data residing in the tables.
6. The fact about the data is stored in form of a table and is termed as Metadata.
5. A file containing metadata is called Data Dictionary.
6. Some of the useful data types supported by ANSI SQL are -
 char (n): Fixed-length character data (string), n characters long. The
maximum size forn is 255 bytes (2000 in Oracle8). Note that a string of type
char is always padded on right with blanks to full length of n.
 varchar2(n): Variable-length character string. The maximum size for n is
2000 (4000 inOracle8). Only the bytes used for a string require storage.
Example: varchar2(80).
 number(n, d): Numeric data type for integers and reals. n- overall number of
digits, d- number of digits to the right of the decimal point. Data types
derived
from number are int[eger], dec[imal], smallint and real.
 date: Date data type for storing date and time. The default format for a date
is: DD-MM-YY. Examples: ’13-OCT-94’, ’07-JAN-98’.
7. Constraints are used to enforce rules at table level when ever row is inserted,
updated/deleted from table. Some useful constraints are NOT NULL, UNIQUE ,
PRIMARY KEY and DEFAULT.

8. In many cases heading of columns may not be descriptive and hence it difficult to

understand. In such case we use columns alias. It will display column heading with

column alias.

9. In a table the order of row and column is not relevant. It has all unique tuples.

1
10.The SQL command for creating an empty table has the following form:

create table <table> (

<column 1><data type> [not null] [unique] [<column constraint>],

.........

<column n><data type> [not null] [unique] [<column constraint>],

[<table constraint(s)>]

);

11. To insert a tuple into a table is to use the insert statement-

insert into <table> [(<column i, . . . , column j>)]

values (<value i, . . . , value j>);

12. In order to retrieve the information stored in the database, the SQL query language is

used.

select [distinct] <column(s)>

from <table>

[ where<condition> ]

[ order by <column(s) [asc|desc]> ]

[ group by <column(s) ]

[ having<condition>]

13. To set conditions some special operators - between and , like ,is null , is not null , in

are used along with usual set of operator.

14. Aggregate functions are statistical functions such as count, min, max, sum ,avg etc. They

are used to compute a single value from a set of attribute values of a column:

15.For modifying attribute values of (some) tuples in a table, we use the update statement:

update<table> set <column i> = <expression i>, . . . ,<column j> = <expression j>

[where <condition>];

2
16.All or selected tuples can be deleted from a table using the delete command:

delete from <table> [where <condition>];

17. To Join Relations name both relations in from clause and set a comparisons in the where

clause between the common fields of both relations. For eg.

select ENAME, E.DEPTNO, DNAME

from EMP E, DEPT D

where E.DEPTNO = D.DEPTNO

and JOB = ’SALESMAN’;

18. having is a sub clause of group by and can not be used independently

19. Where clause imposes condition on entire table where as having imposes condition on

groups.

20. A query containing a group by clause is processed in the following way:

● Select all rows that satisfy the condition specified in the where clause.
● From these rows form groups according to the group by clause.
● Discard all groups that do not satisfy the condition in the having clause.
● Apply aggregate functions to each group.
● Retrieve values for the columns and aggregations listed in the select clause.
22. SQL is a Structured Query Language is based on a relational model. "A Relational Model
of Data for Large Shared Data Banks. SQL is often pronounced like "sequel". SQL
became a standard of the American National Standards Institute (ANSI).
23. The Python standard for database interfaces is the Python DB-API, which is used by
Python database interfaces. The DB-API has been defined as a common interface,
which can be used to access relational databases.
24. The DB API provides a minimal standard for working with databases using Python
structures and syntax.This API includes the following:
 Importing the API module.
 Acquiring a connection with the database.
 Issuing SQL statements and stored procedures.
 Closing the connection

3
25.To use MySQL database using Python, we need to install on our machine; then just
type the script given below to implement MySQL within your program.

import MySQLdb
26. Opening a database connection and “stud” table:
db = MySQLdb.connect ("localhost","testprog","stud","PYDB")
27. The SQL syntax for creating a table "Employee" in the database "Company”:
CREATE TABLE Employee (
staff_number INT NOT NULL AUTO_INCREMENT,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE,
birth_date DATE,
PRIMARY KEY (staff_number) );
To do this directly from Python. To be capable to send a command to "SQL"we need a
cursor object. Usually, a cursor in SQL and databases is a control structure to traverse
over the records in a database.
We can define a SQL command with a triple quoted string in Python:
sql_command = """
CREATE TABLE Employee (
staff_number INTEGER PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(30),
gender CHAR(1),
joining DATE,
birth_date DATE);"""
28. To populate the table we will have to use the "INSERT" command. Example:
sql_command = """INSERT INTO Employee (staff_number, fname, lname, gender,
birth_date)VALUES (NULL, "Williams", "Shakespeare", "m", "1961-10-25");"""
cursor.execute(sql_command)
29. To define the cursor object:

4
 cursor = conn.cursor
30. Benefits of Python Database Programming:
 Programming in Python is considerably simple and efficient with compared to
other languages, so as the database programming.
 Python database is portable, and the program is also portable so both can give
an advantage in case of portability.
 Python supports SQL cursors.
 It also supports Relational Database systems.
 The API of Python for the database is compatible with other databases also.
 It is platform independent.

1 marks Questions :
Q1.Define the following terms:
(i) DBMS (ii) RDBMS (iii) Data Security (iv) Data Integrity (v) Relation
(vi) Domain (vii) Tuple (viii) Attribute (ix) Degree (x) Cardinality
Q2. What is data redundancy? What are the problems associated with it?

Q3How do database management systems ensure data security and privacy?

Q4. What do you mean by referential Integrity? How it is enforced in DBMS?

Q5. What is Key?

Q6. Define the following keys:

(i) Primary key (ii) Candidate key (iii) Alternate key (iv) Foreign key.

Q7.What types of commands are used in the following categories?

1. DDL 2. DML 3. TCL

Q8.What do you mean by Data dictionary? What it consist of?

Q9.What is data type? Name some data types available in MySQL.

Q10.Compare Char and Varchar data types?

Q11.Which keyword eliminates the redundant data from a query result?

Q12.How would you display system date as the result of a query?

Q13. What command is used for:

5
 1. To change/open a database

2. To view the table structure

Q14. What is SQL? What are different categories of commands available in SQL?

Q15. Differentiate between DDL and DML commands?

Q16. Differentiate between Where and having clause of Select statement.

Q17. Name any two data types to store numeric data.

Q18. What do you understand by constraints?

Q19. Write a query on the customers table whose output will exclude all customers with a

Rating <=100, unless they are located in Shimla.

Structure of Customer Table: (Cust_Id, Cust_Name, Rating, Location)

Q20. Write a query that selects all orders except those zeros or NULLs in the amount field.

Q21. Write a query that lists customers in descending order of rating. Output the rating field

first, followed by the customer’s name and number.

Q22. Write a command that puts the following values, in their given order, into the
salesman

table:

Cust-Name-Manisha, City-Manali, Comm- NULL, Cust-Num-1901

Structure of Salesman Table: (Cust-Name, City, Comm, Cust-Num)

Q23.Using standard sample table EMP provided by Oracle, Write following queries-

Table structure is as follows –

Empnonumber(4)

Enamechar(20)

Job char(30) // Profile

Mgr number(4) // manager number

Hiredate date // date of joining

Sal number(8) // Salary

6
Commnumber(6) // Commision

Deptnonumber(4) // department Number

i. Display all records from table.

ii. Display empno and name of all the employees
iii. Display employee name, salary ,and salary + commission from the table
iv. Display Emp no, name and salary for the employee who are not getting
commission.
v. List all unique department numbers.
vi. List all employee details who have four lettered name
vii. List all employees whose annual salary is between 250,000 and 300000.
viii. List all job types offered to the employee
ix. Show the average salary for all departments with more than 3 people for a job.
x. Display only the jobs with maximum salary greater than or equal to 3000.
xi. Find out number of employees having “Manager” as job.
xii. List the count of employees grouped by deptno.
xiii. List the sum of employees’ salaries grouped by department.
xiv. List the maximum salary of employee grouped by their department number.

Q24. Write SQL statement to modify table structure such that it adds a column age in the

student table?

Q25.Write a query to find the list of employee’s whose age is greater than 30 or so given a

date of birth.

Structure of Employee Table: (Emp_Id, Emp_Name, DOB)

Q26. A Table named “Students” which contains studentid, subjectid and marks. Where there

are 10 subjects and 50 students. what is the Query to find out the Maximum marks

obtained in each subject.

Q27. Name the constraint used to set foreign key.

Q28.What is Primary key? What is PRIMARY KEY constraint?

Q29.What is the role of UNIQUE constraint? How is PRIMARY KEY constraint different

7
from UNIQUEconstraint?

Q30.When column’s value is skipped in an INSERT command, which value is inserted in

the database?

Q 31. Which method is used to retrieve all rows and single row? Using connectivity

2 Marks Questions -

Q1. What is Database Management System? Discuss its advantages.

Q2. What is Data Abstraction? Draw a diagram explaining various levels of data abstraction.

Q3. What is MySQL? Describe its features?

Q4. Differentiate between DDL and DML commands?

Q5. How constraints ensure the validity of data? Explain various types of constraints with

example.

Q6. What are the benefits of Python database Programming?

Q7. How to Create and Insert values in Python interface in MYSQL?

Q8. What is database connectivity? Which package must be imported in python to create a
database
connectivity application?

Q9.

Consider the table‘empsalary’.

To select tuples with some esalary, Arun has written the following erroneous SQL
statement:
SELECT eid, salary FROMempsalary WHERE esalary=something;
Write the correct SQL statement.

Q 10. What are the basic steps to connect Python with MYSQL using table Members present
in the database‘Society’?

8
Q 11. Write a MySQL-Python connectivity to retrieve data, one record at a time, from city
table for employees with id less than10.

4 to 6 Marks Questions

Q1. Given below a table:

TABLE: GRADUATE

S.NO. NAME STIPEND SUBJECT AVERAGE DIVISION

1. Karan 400 Physics 68 1

2. Divakar 450 Computer Sc. 68 1
3. Divya 300 Chemistry 62 2
4. Arun 350 Physics 63 1
5. Sabina 500 Mathematics 70 1
6. John 400 Chemistry 55 2
7. Robert 250 Physics 64 1
8. Rubina 450 Mathematics 68 1
9. Vikas 500 Computer Sc. 62 1
10. Mohan 300 Mathematics 57 2
Give the output of following SQL statement based on table GRADUATE:

(i) Select min(average) from Graduate where subject = ‘Physics’;

(ii) Select sum (stipend) from Graduate where div = 2;

(iii) Select avg(stipend) from Graduate where average >= 65;

(iv) Select count(distinct subject) from Graduate;

Q2. Write Queries for the following based on the table Graduate:

i. Find number of graduates in each division

ii. Find average stipend Given subject wise
iii. List details of graduate who has highest average
iv. Display all physics graduates Whose average falls between 60-70
v. Display all students who have graduated from mathematics and computer
sc.
Q3.Write following queries based on the table:

Table: SPORTS

Studen Class Name Game1 Grade Game2 Grade2

t NO
10 7 Sammer Cricket B Swimming A
11 8 Sujit Tennis A Skating C

9
 12 7 Kamal Swimming B Football B
13 7 Venna Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A Atheletics C

(i) Display the names of the students who have grade ‘C’ in either Game1 or
Game2 or both.
(ii) Display the number of students getting grade ‘A’ in Cricket.
(iii.) Display the names of the students who have same game for both Game1 and
Game2.
(iv) Display the games taken up by the students, whose name starts with ‘A’.
(v) Assign a value 200 for Marks for all those who are getting grade ‘B’ or grade ‘A’ in
both Game1 and Game2.
(vi) Arrange the whole table in the alphabetical order of Name.
(vii) Add a new column named ‘Marks’.

Q4. Given below two tables:

Table: Book
Book_id Book name Author_name Publisher Price Type Quantity

C0001 Fast Cook Lata Kapoor EPB 355 Cookery 5

William
F0001 The Tears Hopkins First Publi. 650 Fiction 20

Brain &
T0001 My First c++ Brooke FPB 350 Text 10

T0002 C++ Brain works A.W. Rossaine TDH 350 Text 15

F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 50

Table: issued
Book_Id Quantity Issued

T0001 4

C0001 5

F0001 2

10
Write SQL query for (i) to (vi)

i. To show book name, Author name and price of books of First Pub. Publisher
ii. To list the names from books of text type
iii. To Display the names and price from books in ascending order of their prices.
iv. To increase the price of all books of EPB publishers by 50.
v. To display the Book_Id, Book_name and quantity issued for all books which have
been issued
vi. To insert a new row in the table issued having the following data. ‘F0003’, 1
Give the output of the following
i. Select Count(*) from Books
ii. Select Max(Price) from books where quantity >=15
iii. Select book_name, author_name from books where publishers=’first publ.’
iv. Select count(distinct publishers) from books where Price>=400

Q5.Given below a table:

Table: Employees

Empid Firstname Lastname Address City

010 Ravi Kumar Raj nagar GZB

105 Harry Waltor Gandhi nagar GZB

152 Sam Tones 33 Elm St. Paris

215 Sarah Ackerman 440 U.S. 110 Upton

244 Manila Sengupta 24Friends street New Delhi

300 Robert Samuel 9 Fifth Cross Washington

335 Ritu Tondon Shastri Nagar GZB

400 Rachel Lee 121 Harrison St. New York

441 Peter Thompson 11 Red Road Paris

11
 Table: EmpSalary

Empid Salary Benefits Designation

010 75000 15000 Manager

105 65000 15000 Manager

152 80000 25000 Director

215 75000 12500 Manager

244 50000 12000 Clerk

300 45000 10000 Clerk

335 40000 10000 Clerk

400 32000 7500 Salesman

441 28000 7500 salesman

Write the SQL commands for the following:

i. To show firstname,lastname,address and city of all employees living in paris

ii. To display the content of Employees table in descending order of Firstname.
iii. To display the firstname,lastname and total salary of all managers from the tables
Employee and empsalary , where total salary is calculated as salary+benefits.
iv. To display the maximum salary among managers and clerks from the table
Empsalary.
v. Give the Output of following SQL commands:
i. Select firstname,salary from employees ,empsalary where designation =
‘Salesman’
and Employees.empid=Empsalary.empid;
ii. Select count(distinct designation) from empsalary;
iii. Select designation, sum(salary) from empsalary group by designation having

count(*) >2;
iv. Select sum(benefits) from empsalary where designation =’Clerk’;

12
 MCQ:
1. Which of the following statements are TRUE about an SQL query?
P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY
clause
Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause
R : All attributes used in the GROUP BY clause must appear in the SELECT clause
S : Not all attributes used in the GROUP BY clause need to appear in the SELECT
clause
(A) P and R
(B) P and S
(C) Q and R
(D) Q and S
2. Which is the subset of SQL commands used to manipulate Oracle Database
structures, including tables?

A. Data Definition Language(DDL)

B. Data Manipulation Language(DML)
C. Both of above
D. None

3. Which operator performs pattern matching?

A. BETWEEN operator
B. LIKE operator
C. EXISTS operator
D. None of these

4. What operator tests column for the absence of data?

A. EXISTS operator
B. NOT operator
C. IS NULL operator
D. None of these

5. In SQL, which command(s) is(are) used to change a table's storage characteristics?

A. ALTER TABLE
B. MODIFY TABLE
C. CHANGE TABLE
D. All of the above

6. In SQL, which of the following is not a data definition language commands?

A. RENAME
B. REVOKE
C. GRANT

13
 D. UPDATE

7. In SQL, which command is used to SELECT only one copy of each set of duplicable
rows

A. SELECT DISTINCT
B. SELECT UNIQUE
C. SELECT DIFFERENT
D. All of the above
8. A command that lets you change one or more fields in a record is

A. Insert
B. Modify
C. Look-up
D. All of the above

9. Which SQL keyword is used to retrieve only unique values?

A. DISTINCTIVE
B. UNIQUE
C. DISTINCT
D. DIFFERENT

10. What is a view?

A. A view is a special stored procedure executed when certain event occurs.

B. A view is a virtual table which results of executing a pre-compiled query. A view is
not part of the physical database schema, while the regular tables are.
C. A view is a database diagram.
D. None of these

11. The SQL statement

SELECT SUBSTR('123456789', INSTR('abcabcabc', 'b'), 4) FROM DUAL;

A. 6789
B. 2345
C. 1234
D. 456789

12. Which of the following group functions ignore NULL values?

A. MAX
B. COUNT
C. SUM
D. All of the above

14
13. Which of the following command makes the updates performed by the transaction
permanent in the database?

A. ROLLBACK
B. COMMIT
C. TRUNCATE
D. DELETE

14. Which command undo all the updates performed by the SQL in the transaction?

A. ROLLBACK
B. COMMIT
C. TRUNCATE
D. DELETE

15. What is the meaning of LIKE '%0%0%'

A. Feature begins with two 0's

B. Feature ends with two 0's
C. Feature has more than two 0's
D. Feature has two 0's in it, at any position

16. NULL is

A. the same as 0 for integer

B. the same as blank for character
C. the same as 0 for integer and blank for character
D. not a value

17. Find the names of these cities with temperature and condition whose condition is
neither sunny nor cloudy

A. SELECT city, temperature, condition FROM weather WHERE condition NOT IN

('sunny', 'cloudy');
B. SELECT city, temperature, condition FROM weather WHERE condition NOT
BETWEEN ('sunny', 'cloudy');
C. SELECT city, temperature, condition FROM weather WHERE condition IN ('sunny',
'cloudy');
D. SELECT city, temperature, condition FROM weather WHERE condition BETWEEN
('sunny', 'cloudy');

18. ............. joins two or more tables based on a specified column value not equaling a
specified column value in another table.

A. EQUIJOIN
B. NON-EQUIJOIN

15
 C. OUTER JOIN
D. NATURAL JOIN

19. 'AS' clause is used in SQL for

A. Selection operation.
B. Rename operation.
C. Join operation.
D. Projection operation.

20. Which is/are correct statements about primary key of a table?

A. Primary keys can contain NULL values
B. Primary keys cannot contain NULL values.
C. A table can have only one primary key with single or multiple fields
D. A table can have multiple primary keys with single or multiple fields

21. SQL Query to delete all rows in a table without deleting the table (structure,
attributes, and indexes)
A. DELETE FROM table_name;
B. DELETE TABLE table_name;
C. DROP TABLE table_name;
D. NONE

22. Wrong statement about UPDATE keyword is

A. If WHERE clause in missing in statement the all records will be updated.
B. Only one record can be updated at a time using WHERE clause
C. Multiple records can be updated at a time using WHERE clause
D. None is wrong statement

23. Correct syntax query syntax to drop a column from a table is

A. DELETE COLUMN column_name;
B. DROP COLUMN column_name;
C. ALTER TABLE table_name DROP COLUMN column_name;
D. None is correct.

24. If you want to allow age of a person > 18 in the column Age of table Person, then
which constraint will be applied to AGE column.
A. Default
B. Check
C. NOT NULL
D. None

25. To give a temporary name to a table, or a column in a table for more readability,
what is used?
A. SQL Wildcards
B. SQL aliases
C. SQL LIKES

16
D. SQL Comments

26. What does below SQL statement describe?

SELECT CustomerName, Address + ‘, ‘ + City + ‘, ‘ + Country AS Address
FROM Customers;
A. Alias named Address column will be created and under this Address, City and Country
will be printed as combined statement.
B. Alias named Address will be created for Country columns
C. Alias named Address column will be created and under this all CustomerName, address,
city and country will be printed as combined statement
D. All above options are wrong

27. Which clause is used to combine rows from two or more tables based on a related
column between them.
A. SQL MATCH
B. SQL JOIN
C. SQL PATTERN
D. NONE

28. A SQL query automatically eliminates duplicates (True / False) ?

A.TRUE

B.FALSE

Answers MCQ:

1. Answer: (B)
2. Answer: Option A
3. Answer: Option B
4. Answer: Option C
5. Answer: Option A
6. Answer: Option D
7. Answer: Option A

8. Answer: Option B
9. Answer: Option C
10. Answer: Option B
11. Answer: Option B
Explanation
INSTR Function:- The INSTR function in SQL is used to find the starting location of a pattern
in a string. The syntax for the INSTR function is as follows:

17
INSTR (str, pattern): Find the starting location of pattern in string str.
SUBSTR Function:- The Substring function in SQL is used to grab a portion of the stored
data. The syntax for the SUBSTR function is as follows:
SUBSTR(str,pos,len): Starting with the position pos in string str select the characters upto
the length len.
In the above query,
INSTR('abcabcabc', 'b') outputs 2 as the starting location of pattern
12. Answer: Option D
13. Answer: Option B
14. Answer: Option A
15. Answer: Option D
16. Answer: Option D
17. Answer: Option A
18. Answer: Option B
19. Answer: Option B
20. Answer: B and C
21. Answer: A
22. Answer: B
If you omit the WHERE clause, ALL records will be updated.
23. Answer: C
24. Answer: B
CHECK constraint is used to limit the value range placed in a column
25. Answer: B
26. Answer: A
27. Answer: B
28. Answer : Option B

Python Database

1. Which method is used to Commit pending transaction to the database in Python?

 connection.commit()
 cursor.commit()

18
 2. Which method of cursor class is used to get the number of rows affected after any
of the insert/update/delete database operation executed from Python

 cursor.rowcount
 cursor.getaffectedcount
 cursor.rowscount

3. Which method of cursor class is used to insert or update multiple rows using a
single query

 cursor.executemany(query, rows)
 cursor.execute(query, rows)
 cursor.executmultiple(query, rows)

4. Mandatory arguments required to connect any database from Python

 Username, Password, Hostname, Database Name, Port.

 Username, Password, Hostname
 Username, Password, Hostname, Database Name

5. Which method of cursor class is used to fetch limited rows from the table

 cursor.fetchsize(SIZE)
 cursor.fetchmany(SIZE)
 cursor.fetchonly(SIZE)

Ans:

1. connection.commit()
2. cursor.rowcount
3. cursor.executemany(query, rows)
4. Username, Password, Hostname, Database Name
5. cursor.fetchmany(SIZE)

19