UNIT 3:

Basic structure of SQL queries,

writing simple queries,
Complex queries and nested Subqueries in SQL,
Aggregate functions in SQL,
Effect of NULL values on result,
Defining a Relational Schema,
View definitions and constraints,
Types of keys.
Introduction to PL/SQL.
SQL Introduction: Structured Query Language (SQL) is a standardized
programming language used to manage and manipulate relational databases.
Relational databases store data in a structured, tabular format, with rows and
columns representing records and attributes, respectively.
SQL is the primary language for interacting with RDBMS like MySQL,
PostgreSQL, Oracle, SQL Server, and SQLite.

Why is SQL important?

Structured query language (SQL) is a popular query language that is frequently

used in all types of applications. Data analysts and developers learn and use SQL
because it integrates well with different programming languages.

History of SQL

SQL was invented in the 1970s based on the relational data model. It was initially
known as the structured English query language (SEQUEL). The term was later
shortened to SQL. Oracle, formerly known as Relational Software, became the first
vendor to offer a commercial SQL relational database management system.

SQL Commands are mainly classified into four types,

SQL is mainly divided into four sub languages
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Data Control Language (DCL)

SQL provides commands to perform fundamental data operations:

Data Definition Language (DDL): Used for defining database structures, such
as creating, altering, and dropping tables and other database objects.
 CREATE TABLE: Defines a new table.
ALTER TABLE: Modifies an existing table's structure.
DROP TABLE: Deletes a table.

TRUNCATE – remove all records from a table, including all spaces

allocated for the records are removed.
RENAME – rename an object.

Data Manipulation Language (DML): Used for managing data within tables.
INSERT INTO: Adds new rows of data.
UPDATE: Modifies existing data.
DELETE FROM: Removes rows of data.

Data Query Language (DQL): Used for retrieving data from the database.

SELECT: Retrieves data based on specified criteria.

Data control Language (DCL): can manage the data control from other users in the
database environment.
GRANT – allow users access rights (permissions) to database.
REVOKE – withdraw user’s access rights (permissions)

Transaction Control Language (TCL): Deals with transaction within a database.

COMMIT – commits a Transaction.
ROLLBACK – rollback a transaction in case of any error occurs.
SAVEPOINT – to rollback the transaction making points within groups
 Data Types:
SQL (Structured Query Language) utilizes various data types to define the
kind of data that can be stored in a column within a database table. These data types
ensure data integrity, optimize storage, and facilitate efficient data manipulation.

Categories of SQL Data Types:

 Numeric Data Types:
Used for storing numerical values.
Integers:
INT, INTEGER: Standard integer values.
SMALLINT, TINYINT, BIGINT: Integers with varying storage sizes and
value ranges.
Note: Range of integers is machine dependent.

numeric(p,d) – A fixed point number with user defined precision.

The number consists of ‘p’ digits (plus a sign), and ‘d’ of the ‘p’ digits are
to the right of the decimal point.

Example: numeric(4,2)
It means the total number contains 4 digits and among them 2 are right to
the decimal point.
23.45 – Acceptable.
2.345 – Not acceptable.
234.5 – Not acceptable.

char(n) – Fixed length of character string with user specified length ‘n’.
character(n) is equivalent form.
varchar(n) – Variable length character string with user specified maximum length
‘n’.
character varying(n) is equivalent form.
real, double precision – Floating-point and double- precision floating-point
numbers with machine- dependent precision.
float(n) – A floating-point number, with precision of at least ‘n’ digits.

date – Calendar date that contains

 Year(4 digits).
 Month.
 Day.
Example: ‘2021-07-06’
time – time of the day. It contains
 Hours.
 Minutes.
 Seconds.
Example: ‘09.30.26’

Time zone also can be included by specifying “time with

timezone”.
timestamp – Combination of date and time. It contains
 Date.
 Time.
Example: ‘2021-07-06 09.30.26’

Time zone can be included by specifying “timestamp with timezone”.

Large Objects types –

To store large amount of the data.
 clob – Character Large Object.
 blob – Binary Large Object.
 Examples:
 story clob(20KB)
 Picture blob(5MB)
 Video blob(1GB)

DDL:
 Creating data base:
Syntax:
create database <database_name>;
Example:
create database university;
create database factory;

Displaying all the databases which are available:

Syntax:
show databases;
Example: show databases;

USE command to select a particular database

Using required database:
Syntax:
Use database_name;
Example:
mysql> use customers;
Database changed to customer
Drop Database :
Can drop/delete/remove a MySQL database easily with the MySQL command.
You should be careful while deleting any database because you will lose
your all the data available in your database.
Syntax:
DROP DATABASE database_name;
Example:
DROP DATABASE employees;

TRUNCATE:
Used to remove all rows from a table, effectively emptying it while keeping
the table structure intact. It is faster than DELETE for removing all data as it
deallocates the space occupied by the table's data.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE Students;

RENAME: Used to change the name of a database object, such as a table.

Syntax:
RENAME TABLE old_table_name TO new_table_name;
Example:
RENAME TABLE Students TO EnrolledStudents;

Creating a table/relation:
Syntax:
create < Table_name > { < attribute name datatype>,.....};
Example:
 mysql> create table students( name varchar(25), contact numeric(10,0));

Displaying list of relations:

Syntax:
show tables;
Example:
mysql> show tables;

Displaying schema of relations:

Syntax:
describe <table_name>;
Example:
mysql> describe students;

Constraints in SQL:
A constraint is a mechanism which is used to stop or restrict invalid data into
the table. Constraints can be divided into following two types,
➢ Column level constraints: limits only column data

➢ Table level constraints: limits whole table data

Constraints in SQL:
[Link] NULL.
2. UNIQUE.
[Link]
4. PRIMARY KEY
5. FOREIGN KEY
NOT NULL Constraint: If you do not enter the value in any particular column in
the table that column automatically take null.
Not null Constraints does not allow entering null value on a particular column in a
table.
❖ we can apply not null constraints on more than one column in same table.
Example:
create table students (sid number(6) not null, sname varchar(20));

Unique Constraint:
❖ Unique Constraints does not allow duplicate values.

❖ We can apply unique constraints on more than one column in same table

create table students (sid number(6) not null unique, sname varchar(20) unique);

Default Constraints:
❖ Default Constraints is used to insert a default value into a column.

❖ The default value will be added to all new records, if no other value is specified.

Primary Key Constraints:

❖ It is used to define a key column of a table.

❖ Primary Key Constraints does not allow duplicate as well as null values.

❖ This constraint is supported with an index automatically.

❖ We cannot apply Primary Key constraints on more than one column in same
table.
Primary key= NOT NULL + UNIQUE + INDEX
Example:
create table students (sid number(6) primary key, sname varchar(20) unique);
FOREIGN KEY:
❖ A foreign key a column that is used to establish a link between two tables.

❖ In simple words we can say that, a foreign key is one column in a table that is
used to point primary key column in another table.
❖ It allows NULL and Duplicate values.

❖ It can be related to either primary key or unique constraint column of other

table.
𝒑𝒓𝒊𝒎𝒂𝒓𝒚 𝒌𝒆𝒚 𝒖𝒏𝒊𝒒𝒖𝒆 < − − − − −−> 𝒇𝒐𝒓𝒆𝒊𝒈𝒏 𝒌𝒆𝒚
Example:
SQL>create table emp ( emp_id int(10) primary key, emp_name char(20),
emp_age int(10), salary int(10), dept_no int(10), foreign key (dept_no) references
dept(dept_no) );

Table in the database:

Syntax:
CREATE TABLE table_name (
column_name data_type(size),
column_name data_type(size),
…...);
Example: create a table named "cust" in the database "customers".
CREATE TABLE cust (
cus_id INT NOT NULL AUTO_INCREMENT,
cus_firstname VARCHAR(100) NOT NULL,
cus_surname VARCHAR(100) NOT NULL,
 PRIMARY KEY ( cus_id )
);
Note: You can use multiple columns separated by comma to define a primary key.

SHOW tables; check the tables available in databases

DESCRIBE cust; it will display the schema of the table.

Drop the relation:

Syntax:
drop <table_name>;

Delete a relation:
Syntax:
delete <table_name>;

ALTER Table
MySQL ALTER statement is used when you want to change the name of
your table or any table field. It is also used to add or delete an existing column in a
table.
The ALTER statement is always used with "ADD", "DROP" and
"MODIFY" commands according to the situation.
ADD a column in the table Syntax:

ALTER TABLE table_name ADD new_column_name column_definition

[ FIRST | AFTER column_name ];
Where,
 table_name: It specifies the name of the table that you want to modify.
new_column_name: It specifies the name of the new column that you want
to add to the table.
column_definition: It specifies the data type and definition of the column
(NULL or NOT NULL, etc).
FIRST | AFTER column_name: It is optional.

It tells MySQL where in the table to create the column. If this parameter is
not specified, the new column will be added to the end of the table.

Example:
we add a new column "cus_age" in the existing table "cust".
Use the following query to do this:

ALTER TABLE cust ADD cus_age varchar(40) NOT NULL;

Add multiple columns in the table

Syntax:
ALTER TABLE table_name ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition [ FIRST | AFTER
column_name ],
. ... …… ;

Example:
 we add two new columns "cus_address", and cus_salary in the existing table
"cust". cus_address is added after cus_surname column and cus_salary is added
after cus_age column.

Use the following query to do this:

ALTER TABLE cust ADD cus_address varchar(100) NOT NULL AFTER
cus_surname, ADD cus_salary int(100) NOT NULL AFTER cus_age ;

MODIFY column:
The MODIFY command is used to change the column definition of the
table.
Syntax: ALTER TABLE table_name MODIFY column_name
column_definition [ FIRST | AFTER column_name ];

Example: we modify the column cus_surname to be a data type of varchar(50) and

force the column to allow NULL values
Example:
ALTER TABLE cust MODIFY cus_surname varchar(50) NULL;

DROP column in table

Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
example to drop the column name "cus_address" from the table "cust".
Example:
ALTER TABLE cust DROP COLUMN cus_address;

RENAME column:
Syntax:
ALTER TABLE table_name rename COLUMN old_column_name TO
new_column_name;
Example: change the column name "cus_surname" to "cus_title".
Example:
ALTER TABLE cust rename COLUMN cus_surname TO cus_title
varchar(20) NOT NULL

RENAME table:
Syntax:
ALTER TABLE old_table_name RENAME TO new_table_name;
Example: the table name cust is renamed as cust_table.
ALTER TABLE cus_tbl RENAME TO cus_table;

TRUNCATE Table vs DROP Table

DROP TABLE command to delete complete table but it will remove complete
table data and structure both.
You need to re-create the table again if you have to store some data.
In the case of TRUNCATE
Removes all rows from a table by preserving its structure like schema,
constraints.. ect

Faster than delete command

Re creates table and reset to auto indexing
Cannot roll back
DML: Data Manipulation Language
Insert: MySQL INSERT is used to insert data in MySQL table within the
database. We can insert single or multiple records using a single query in MySQL.
Syntax:
INSERT INTO <Table_name> VALUES (V1, V2, . . .Vn );
(or)
INSERT INTO <Table_name> (A1, A2, . . . ,An) VALUES (V1, V2, . . . );

Example:
INSERT INTO Students VALUES (101,Shirisha, CSE);
(or)
INSERT INTO Students (ID, NAME, Dept)

VALUES (101,’Shirisha’,’CSE’);

Field name specification is not mandatory for inserting all field values. But not in
case of inserting partial field (some fields of table) values.

INSERT INTO Students (ID, NAME) VALUES (102, ‘Bheema’)

We can insert single or multiple records using a single query in MySQL.

INSERT INTO Students (ID, NAME, Dept) VALUES (103, ‘shiva’,’CSE’),
(104,’prasad’,’ECE’), (105,’Kundana’,’ME’);
To check the data present in the table student,
Then,
Select * from Student;
Update:
MySQL UPDATE command is used to update data of the table within the database.
It is used when you need to modify the table.

Syntax:
Update <Table_name>
SET <Attribute_name> = <value>, . . . . . Where <logic>;

Example:
Update student SET Dept = ‘ECE’ where id=101;
More than one filed of the table can be updated.
Update student SET Dept =’ECE’, NAME = ‘Shireesha’ where
id=101;
Where clause is used to update the specific field of the table otherwise
the changes will made for the entire table.

To see changes,
Select * from Student;

Deletion
DELETE commandt is used to delete data from the table within the database.

By using delete statement, we can delete specifi record from the table.

Syntax:
Delete From <Table_name>Where <logic>;
Example:
Delete From Student where id=101;
Deletion
Truncate <Table_name>;
Example:
Truncate Student;
DQL:
SELECT command is used to fetch data from the one or more tables in MySQL. We can retrieve records of
all fields or specific fields on our requirement. It filters records.

Syntax:
Select A1, A2, A3,......An

From R1, R2, R3,......Rm

Where <Logic>

Example:
Select id, name, dept from student;
Or
Select * from students;
Select id, name from student where dept=’cse’;