2nd PUC Computer Science Chapter 9 SQL Notes | Structured Query Language

9.1 Introduction

9.2 Structured Query Language (SQL)

In a file system, application programs are required to access data. Database management systems provide special languages called query languages for accessing and manipulating database data.

SQL is widely used with relational database management systems such as MySQL, ORACLE and SQL Server.

Important Features of SQL

• SQL statements comprise descriptive English words.
• SQL is not case sensitive.
• It allows us to create and interact with a database.
• We specify what data is to be retrieved rather than how to retrieve it.
• SQL provides statements for defining data structure.
• SQL provides statements for manipulating data.
• SQL supports declaration of constraints.
• SQL provides different ways of retrieving data according to requirements.

9.2.1 Installing MySQL

MySQL is an open source RDBMS software. The textbook states that it can be downloaded from the MySQL official website. After installing MySQL, the MySQL service is started. The appearance of the mysql> prompt indicates that MySQL is ready to accept SQL statements.

9.3 Data Types and Constraints in MySQL

A database consists of one or more relations. A relation or table consists of attributes or columns. Each attribute has a data type, and constraints can also be specified for attributes.

9.3.1 Data Type of Attribute

For example, arithmetic operations can be performed on numeric data but not on character data.

Commonly Used MySQL Data Types

CHAR and VARCHAR

9.3.2 Constraints

9.4 SQL for Data Definition

Before storing data, the relation schema has to be defined. This includes creating a relation, naming it, identifying attributes, deciding data types and specifying constraints.

SQL statements used for defining, modifying and deleting relation schemas are part of Data Definition Language (DDL).

9.4.1 CREATE Database

The CREATE DATABASE statement is used to create a database.

CREATE DATABASE databasename;

mysql> CREATE DATABASE StudentAttendance;
Query OK, 1 row affected (0.02 sec)

To select a database for use:

mysql> USE StudentAttendance;
Database changed

To know the names of existing databases:

SHOW DATABASES;

To see the tables in the selected database:

SHOW TABLES;

9.4.2 CREATE Table

After creating the database, relations are created by specifying attributes, data types and constraints.

CREATE TABLE tablename(
attributename1 datatype constraint,
attributename2 datatype constraint,
:
attributenameN datatype constraint);

Important Points

• The number of columns defines the degree of the relation, denoted by N.
• Attribute name specifies the name of a column.
• Datatype specifies the type of data an attribute can hold.
• Constraint specifies restrictions on attribute values.
• By default, an attribute can take NULL values except for a primary key.

STUDENT, GUARDIAN and ATTENDANCE Tables

*AttendanceDate and RollNumber together form the composite primary key.

Example 9.1 — Create STUDENT

mysql> CREATE TABLE STUDENT(
 -> RollNumber INT,
 -> SName VARCHAR(20),
 -> SDateofBirth DATE,
 -> GUID CHAR (12),
 -> PRIMARY KEY (RollNumber));

9.4.3 DESCRIBE Table

The structure of an already created table can be viewed using the DESCRIBE or DESC statement.

DESCRIBE tablename;

mysql> DESCRIBE STUDENT;

9.4.4 ALTER Table

ALTER TABLE is used when the structure of a table has to be changed. It can be used to add or remove attributes, modify data types and add or remove constraints.

A. Add Primary Key

ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);

The ATTENDANCE table has a composite primary key:

ALTER TABLE ATTENDANCE
-> ADD PRIMARY KEY(AttendanceDate,
RollNumber);

B. Add Foreign Key

Important conditions:

• The referenced relation must already be created.
• The referenced attribute(s) must be part of the primary key of the referenced relation.
• The data types and sizes of referenced and referencing attributes must be the same.

ALTER TABLE table_name ADD FOREIGN KEY(attribute name)
REFERENCES referenced_table_name(attribute name);

ALTER TABLE STUDENT
-> ADD FOREIGN KEY(GUID) REFERENCES
-> GUARDIAN(GUID);

C. Add UNIQUE Constraint

ALTER TABLE table_name ADD UNIQUE (attribute name);

ALTER TABLE GUARDIAN
-> ADD UNIQUE(GPhone);

D. Add an Attribute

ALTER TABLE table_name ADD attribute name DATATYPE;

ALTER TABLE GUARDIAN
-> ADD income INT;

E. Modify Data Type

ALTER TABLE table_name MODIFY attribute DATATYPE;

ALTER TABLE GUARDIAN
-> MODIFY GAddress VARCHAR(40);

F. Modify Constraint

ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;

ALTER TABLE STUDENT
-> MODIFY SName VARCHAR(20) NOT NULL;

G. Add Default Value

ALTER TABLE table_name MODIFY attribute DATATYPE
DEFAULT default_value;

ALTER TABLE STUDENT
-> MODIFY SDateofBirth DATE DEFAULT '2000-05-15';

H. Remove an Attribute

ALTER TABLE table_name DROP attribute;

ALTER TABLE GUARDIAN DROP income;

I. Remove Primary Key

ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE GUARDIAN DROP PRIMARY KEY;

9.4.5 DROP Statement

DROP is used to permanently remove a table or database. The textbook specifically cautions that the operation cannot be undone.

DROP TABLE table_name;

DROP DATABASE database_name;

Dropping a database ultimately removes all the tables within it.

9.5 SQL for Data Manipulation

When a table is created, only its structure is created and it initially contains no data. SQL statements are used to insert, delete and update records.

9.5.1 Insertion of Records

INSERT INTO is used to insert new records into a table.

INSERT INTO tablename
VALUES(value 1, value 2,....);

Each value corresponds to the corresponding attribute. Attribute names need not be specified when the number of values exactly matches the total number of attributes.

Insert Record into GUARDIAN

INSERT INTO GUARDIAN
-> VALUES (444444444444, 'Amit Ahuja',
5711492685, 'G-35,Ashok vihar, Delhi' );

To insert values only into selected attributes:

INSERT INTO tablename (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT INTO GUARDIAN(GUID, GName, GAddress)
-> VALUES (333333333333, 'Danny Dsouza',
'S -13, Ashok Village, Daman' );

STUDENT Table Example

INSERT INTO STUDENT
-> VALUES(1,'Atharv Ahuja','2003-05-15',
444444444444);

INSERT INTO STUDENT (RollNumber, SName,
SDateofBirth, GUID)
-> VALUES (1,'Atharv Ahuja','2003-05-15',
444444444444);

Dates are stored in YYYY-MM-DD format.

If GUID is NULL and column names are not specified:

INSERT INTO STUDENT
-> VALUES(3, 'Taleem Shah','2002-02-28', NULL);

Alternatively, when only selected attributes are being inserted:

INSERT INTO STUDENT (RollNumber, SName,
-> SDateofBirth) VALUES (3, 'Taleem Shah','2002-02-28');

9.6 SQL for Data Query

The SELECT statement is used to retrieve data from tables. It is also called a query statement. The output is displayed in tabular form.

9.6.1 SELECT Statement

SELECT attribute1, attribute2, ...
FROM table_name
WHERE condition;

• The attributes are the column names to be retrieved.
• The FROM clause specifies the table from which data is retrieved.
• The WHERE clause is optional and specifies conditions for retrieval.

Select All Data

SELECT * FROM table_name;

Example 9.2

Retrieve the name and date of birth of the student with roll number 1:

SELECT SName, SDateofBirth
FROM STUDENT
WHERE RollNumber = 1;

9.6.2 Querying Using Database OFFICE

The textbook introduces an OFFICE database containing related tables such as EMPLOYEE and DEPARTMENT. An employee is assigned to a department and the department number, DeptId, is stored as a foreign key in EMPLOYEE.

A. Retrieve Selected Columns

SELECT EmpNo FROM EMPLOYEE;

SELECT EmpNo, Ename FROM EMPLOYEE;

B. Renaming Columns Using AS

The AS keyword can be used to rename a column while displaying query output.

SELECT EName as Name FROM EMPLOYEE;

Example 9.3

SELECT EName as Name, Salary*12 FROM EMPLOYEE;

The result can use an alias for the calculated column:

SELECT Ename AS Name, Salary*12 AS 'Annual Income'
FROM EMPLOYEE;

C. DISTINCT Clause

DISTINCT eliminates repetition from the retrieved result.

SELECT DISTINCT DeptId FROM EMPLOYEE;

D. WHERE Clause

WHERE retrieves records that satisfy specified conditions.

Relational operators mentioned in the chapter are:

< <= > >= != =

Logical operators:

AND OR NOT

Example 9.4

SELECT * FROM EMPLOYEE
WHERE Salary > 5000 AND DeptId = 'D04';

Example 9.5

SELECT * FROM EMPLOYEE
WHERE NOT Ename = 'Aaliya';

Example 9.6 — Salary Range

SELECT Ename, DeptId
FROM EMPLOYEE
WHERE Salary>=20000 AND Salary<=50000;

The same range can be checked using BETWEEN:

SELECT Ename, DeptId
FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 50000;

Example 9.7 — OR

SELECT *
FROM EMPLOYEE
WHERE DeptId = 'D01' OR DeptId = 'D02' OR
DeptId = 'D04';

E. Membership Operator IN

SELECT * FROM EMPLOYEE
WHERE DeptId IN ('D01', 'D02' , 'D04');

NOT can be combined with IN:

SELECT * FROM EMPLOYEE
WHERE DeptId NOT IN('D01', 'D02');

F. ORDER BY Clause

ORDER BY displays data in an ordered form according to a specified column.

• By default, the order is ascending.
• DESC is used for descending order.

SELECT * FROM EMPLOYEE
ORDER BY Salary;

SELECT * FROM EMPLOYEE
ORDER BY Salary DESC;

G. Handling NULL Values

Arithmetic operations involving NULL result in NULL. For example, the textbook gives:

5 + NULL = NULL

To test for NULL:

SELECT * FROM EMPLOYEE
WHERE Bonus IS NULL;

To test for non-NULL values:

SELECT EName FROM EMPLOYEE
WHERE Bonus IS NOT NULL
AND DeptID = 'D01';

H. Substring Pattern Matching

Sometimes exact matching is not required. SQL provides the LIKE operator with WHERE for pattern matching.

Example 9.13

SELECT * FROM EMPLOYEE
WHERE Ename like 'K%';

Example 9.14

SELECT * FROM EMPLOYEE
WHERE Ename like '%a'
AND Salary > 45000;

Example 9.15

SELECT * FROM EMPLOYEE
WHERE Ename like '_ANYA';

Example 9.16

SELECT Ename FROM EMPLOYEE
WHERE Ename like '%se%';

Example 9.17

SELECT EName FROM EMPLOYEE
WHERE Ename like '_a%';

9.7 Data Updation and Deletion

Updation and deletion are also part of SQL Data Manipulation Language (DML).

9.7.1 Data Updation

UPDATE table_name
SET attribute1 = value1, attribute2 = value2, ...
WHERE condition;

Example

UPDATE STUDENT
-> SET GUID = 101010101010
-> WHERE RollNumber = 3;

Updating More Than One Column

UPDATE GUARDIAN
-> SET GAddress = 'WZ - 68, Azad Avenue,
-> Bijnour, MP', GPhone = 9010810547
-> WHERE GUID = 466444444666;

9.7.2 Data Deletion

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM STUDENT WHERE RollNumber = 2;

9.8 Functions in SQL

A function performs a particular task and returns zero or more values as a result. SQL functions can work on single records or multiple records.

Categories of Single-Row Functions

9.8.1 Single Row Functions

Math functions accept numeric values and return numeric values. String functions accept character values and return character or numeric values. Date and Time functions accept date and time values and return numeric, string, or date/time values.

A. Math Functions

Example 9.18 — GST

SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;

To add FinalPrice:

ALTER TABLE INVENTORY ADD(FinalPrice Numeric(10,1));

To calculate it:

UPDATE INVENTORY SET
FinalPrice=Price+Round(Price*12/100,1);

EMI and Remaining Amount

SELECT CarId, FinalPrice,
ROUND(FinalPrice-MOD(FinalPrice,1000)/10,0) "EMI",
MOD(FinalPrice,10000) "Remaining Amount"
FROM INVENTORY;

Example 9.19 — Commission

ALTER TABLE SALE ADD(Commission Numeric(7,2));

UPDATE SALE SET
Commission=12/100*SalePrice;

SELECT * FROM SALE WHERE Commission > 73000;

SELECT InvoiceNo, SalePrice,
Round(Commission,0) FROM SALE;

B. String Functions

String functions operate on alphanumeric data. They can change case, extract substrings, calculate string length and perform other operations.

Example 9.20

SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;

Find email length and the portion before @:

SELECT LENGTH(Email), LEFT(Email, INSTR(Email, "@")-1)
FROM CUSTOMER;

Find the area code from the customer in Rohini:

SELECT MID(Phone,3,4) FROM CUSTOMER
WHERE CustAdd like '%Rohini%';

Remove .com from email addresses:

SELECT TRIM(".com" from Email) FROM CUSTOMER;

Display customers having Yahoo email:

SELECT * FROM CUSTOMER WHERE Email LIKE "%yahoo%";

C. Date and Time Functions

Example 9.21

SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ)
FROM EMPLOYEE;

To display information for employees whose joining day is not Sunday:

SELECT DAYNAME(DOJ), DAY(DOJ),
MONTHNAME(DOJ), YEAR(DOJ)
FROM EMPLOYEE
WHERE DAYNAME(DOJ)!='Sunday';

9.8.2 Aggregate Functions

COUNT Examples

SELECT COUNT(*) FROM INVENTORY
WHERE Model="VXI";

SELECT COUNT(DISTINCT Model) FROM INVENTORY;

SELECT AVG(Price) FROM INVENTORY
WHERE Model="LXI";

9.9 GROUP BY Clause in SQL

GROUP BY is used when rows need to be grouped according to common values in a specified column.

The HAVING clause is used to specify conditions on rows with GROUP BY.

Example 9.23

Number of cars purchased by each customer:

SELECT CustID, COUNT(*) "Number of Cars"
FROM SALE GROUP BY CustID;

Customers who purchased more than one car:

SELECT CustID, COUNT(*) FROM SALE GROUP BY
CustID HAVING Count(*)>1;

Number of people in each payment mode:

SELECT PaymentMode, COUNT(PaymentMode)
FROM SALE GROUP BY Paymentmode
ORDER BY Paymentmode;

Payment modes used more than once:

SELECT PaymentMode, Count(PaymentMode)
FROM SALE
GROUP BY Paymentmode
HAVING COUNT(*)>1
ORDER BY Paymentmode;

9.10 Operations on Relations

The textbook describes operations such as UNION, INTERSECT, MINUS and Cartesian Product.

9.10.1 UNION (∪)

UNION combines selected rows from two tables. If some rows are common to both tables, they appear only once in the result.

9.10.2 INTERSECT (∩)

INTERSECT returns the common tuples from two tables.

9.10.3 MINUS (-)

MINUS returns rows that are present in the first table but not in the second table.

9.10.4 Cartesian Product (X)

The degree of the resulting relation is the sum of the degrees of the two relations.

The cardinality of the resulting relation is the product of the cardinalities of the two relations.

For DANCE and MUSIC in the textbook:

• Degree of DANCE = 3
• Degree of MUSIC = 3
• Cardinality of DANCE = 4
• Cardinality of MUSIC = 5
• Resulting degree = 3 + 3 = 6
• Resulting cardinality = 4 × 5 = 20

9.11 Using Two Relations in a Query

Queries can use more than one relation.

9.11.1 Cartesian Product on Two Tables

When more than one table is used in a query, table names are separated by commas in the FROM clause.

Example 9.24

SELECT * FROM DANCE, MUSIC;

The result has degree 6 and cardinality 20.

To display only rows where the Name attribute has the same value in both tables:

SELECT * FROM DANCE D, MUSIC M
WHERE D.Name = M.Name;

9.11.2 JOIN on Two Tables

Unlike Cartesian Product, which creates all possible combinations, JOIN combines related tuples based on conditions.

The related attribute is usually a primary key in one table and a foreign key in another.

UNIFORM and COST

UCode is the primary key in UNIFORM. UCode and Size together form the composite key in COST. UCode is the common attribute.

Example 9.25 — JOIN in Three Ways

a) Using condition in WHERE clause

SELECT * FROM UNIFORM U, COST C
WHERE U.UCode = C.UCode;

b) Explicit JOIN clause

SELECT * FROM UNIFORM U JOIN COST C ON
U.Ucode=C.Ucode;

c) NATURAL JOIN

SELECT * FROM UNIFORM NATURAL JOIN COST;

NATURAL JOIN removes the repeated common UCode column from the result.

Important JOIN Points

• If two tables are joined using equality on a common attribute, JOIN with ON or NATURAL JOIN can be used.
• If three tables are joined using equality conditions, two JOIN or NATURAL JOIN operations are required.
• In general, N-1 joins are needed to combine N tables on equality conditions.
• With JOIN, relational operators can be used to combine tuples from two tables.

Chapter Summary

Questions & Answers

1-Mark Questions

2-Mark Questions

3-Mark Questions

4/5-Mark Questions

Important Questions

1 Mark

• Define RDBMS.
• Name any two RDBMS software.
• What is SQL?
• What is the purpose of DISTINCT?
• What does LIKE do?
• What are the two wildcard characters used with LIKE?
• What is NULL?
• Name any two aggregate functions.
• What is GROUP BY?
• What is JOIN?

2 Marks

• Differentiate CHAR and VARCHAR.
• Explain PRIMARY KEY and FOREIGN KEY.
• Explain ORDER BY.
• Explain BETWEEN and IN.
• Explain IS NULL and IS NOT NULL.
• Write the purpose of UPDATE and DELETE.
• State the purpose of the WHERE clause.

3 Marks

• Explain the conditions required while adding a foreign key.
• Differentiate Single Row and Multiple Row functions.
• Explain the wildcard characters of LIKE.
• Explain the purpose of GROUP BY and HAVING.
• Explain UNION, INTERSECT and MINUS.

4/5 Marks

• Explain ALTER TABLE operations with suitable SQL syntax.
• Explain SQL data types and constraints given in the chapter.
• Explain Single Row functions with examples.
• Explain Aggregate functions with examples.
• Explain Cartesian Product and its degree and cardinality.
• Explain JOIN using two tables.
• Explain the three methods of joining UNIFORM and COST.
• Write SQL queries based on the PRODUCT, SPORTS and CARSHOWROOM exercises given in the chapter.

⚡ Quick Revision

Important Definitions

• SQL: Structured Query Language used to access and manipulate database data.
• Constraint: Restriction on data values of an attribute.
• NULL: Represents a missing or unknown value.
• Single Row Function: Operates on a single value and returns a single value.
• Aggregate Function: Operates on a set of records and returns one value for the group.
• GROUP BY: Groups rows having common values in a specified column.
• JOIN: Combines tuples from tables according to specified conditions.
• Cartesian Product: Produces all combinations of tuples from two relations.

Important SQL Statements

Important Operators / Clauses

LIKE Wildcards

Important Functions

Important Differences

Important Relational Operations

Important JOIN Points

• JOIN combines related rows from tables.
• JOIN uses specified conditions.
• NATURAL JOIN removes the redundant common column.
• N-1 joins are needed to combine N tables on equality conditions.

Important Syntax to Revise

CREATE DATABASE databasename;

CREATE TABLE tablename(
attributename1 datatype constraint,
attributename2 datatype constraint,
:
attributenameN datatype constraint);

DESCRIBE tablename;

ALTER TABLE table_name
ADD FOREIGN KEY(attribute name)
REFERENCES referenced_table_name(attribute name);

ALTER TABLE table_name
MODIFY attribute DATATYPE;

ALTER TABLE table_name
DROP attribute;

DROP TABLE table_name;

INSERT INTO tablename
VALUES(value 1, value 2,....);

SELECT attribute1, attribute2, ...
FROM table_name
WHERE condition;

UPDATE table_name
SET attribute1 = value1, attribute2 = value2, ...
WHERE condition;

DELETE FROM table_name
WHERE condition;

SELECT * FROM EMPLOYEE
ORDER BY Salary DESC;

SELECT * FROM EMPLOYEE
WHERE DeptId IN ('D01', 'D02', 'D04');

SELECT * FROM EMPLOYEE
WHERE Bonus IS NULL;

SELECT * FROM EMPLOYEE
WHERE Ename LIKE 'K%';

SELECT CustID, COUNT(*)
FROM SALE
GROUP BY CustID;

SELECT * FROM UNIFORM U JOIN COST C
ON U.Ucode=C.Ucode;

SELECT * FROM UNIFORM NATURAL JOIN COST;

Important Programs / SQL Practice Areas

• Create StudentAttendance database.
• Create STUDENT table.
• Describe STUDENT.
• Add primary and foreign keys using ALTER TABLE.
• Insert records into GUARDIAN and STUDENT.
• Retrieve selected columns using SELECT.
• Use aliases with AS.
• Use DISTINCT and WHERE.
• Use BETWEEN and IN.
• Use ORDER BY.
• Handle NULL values.
• Use LIKE pattern matching.
• Update and delete records.
• Use Math, String and Date functions.
• Use Aggregate functions.
• Use GROUP BY and HAVING.
• Perform UNION, INTERSECT, MINUS and Cartesian Product operations.
• Use Cartesian Product and JOIN with two tables.
• Use NATURAL JOIN.

Important Diagrams / Structures to Revise

• MySQL Shell prompt shown in Figure 9.1.
• CARSHOWROOM database schema shown in Figure 9.2.
• Single Row Function categories shown in Figure 9.3.
• UNION diagram shown in Figure 9.4.
• INTERSECT diagram shown in Figure 9.5.
• MINUS / Difference diagram shown in Figure 9.6.
• Relationships and JOIN structure involving the tables used in the chapter.

Textbook Exercise — Chapter 9

SELECT POW(2,3);

SELECT ROUND(342.9234,-1);

SELECT LENGTH("Informatics Practices");

SELECT YEAR("1979/11/26"),
MONTH("1979/11/26"),
DAY("1979/11/26"),
MONTHNAME("1979/11/26");

SELECT LEFT("INDIA",3),
RIGHT("Computer Science",4),
MID("Informatics",3,4),
SUBSTR("Practices",3);