UNIT-6: MANIPULATING AND Dipesh Koirala

QUERYING DATA
 OUTLINE
❖ Adding Data with INSERT Statement
❖ Retrieving Data with SELECT Statement and FROM Clause and Filter Data with WHERE
Clause
❖ Order and Grouping Data with ORDER and GROUP by Clause and Summarizing the Select
Statement
❖ Retrieving Data from Different Tables using: INNER JOINS, OUTER JOIN and CROSS JOIN
❖ Building Nested Queries
❖ Manipulate Data Using UPDATE Statement and Removing Rows Using DELETE Statement
❖ Creating and Altering View
DATA MANIPULATION LANGUAGE
i. SELECT
ii. INSERT
iii. UPDATE
iv. DELETE
ADDING DATA WITH INSERT STATEMENT
INSERT Statement
❖Syntax: INSERT INTO tablename (column1, column2, ……. ) VALUES
(value1, value2, ……………),
…………………………….. );
SELECT STATEMENT
❖Syntax:
SELECT column1, column2, …… FROM tablename WHERE condition;
UPDATE STATEMENT
❖Syntax:
UPDATE tablename SET column_name = new_value WHERE condition
DELETE STATEMENT
❖Syntax:
DELETE FROM tablename WHERE condition;
DELETE STATEMENT
❖Syntax:
ORDER BY CLAUSE
❖ The order by clause causes the tuples in the result of a query to appear in sorted order.
❖Syntax:
SELECT column1, column2 FROM tablename ORDER BY column ASC/DESC
❖E.g.,
ORDER BY CLAUSE
Using Expression NULL First/ Last
AGGREGATE FUNCTIONS
❖Aggregate functions operate on a multiset of values and return a single value.
❖Typical aggregate functions are min, max, sum, count, and avg.
MIN: Retrieves the minimum value in a column.
Syntax:
SELECT MIN(column_name) FROM table_name;

MAX: Retrieves the maximum value in a column.

Syntax:
SELECT MAX(column_name) FROM table_name;

COUNT: Counts the number of rows in a result set.

Syntax:
SELECT COUNT * FROM table_name;
AGGREGATE FUNCTIONS
SUM: Calculates the sum of values in a column.
Syntax:
SELECT SUM(column_name) FROM table_name;
AVG: Calculates the average (mean) of values in a column.
Syntax:
SELECT AVG(column_name) FROM table_name;
AGGREGATE FUNCTIONS
❖ Suppose a Employee table with following attributes.
AGGREGATE FUNCTIONS
.1. Finds the minimum salary among employees.
SELECT MIN(salary) AS min_salary FROM employees;

2.Finds the maximum salary among employees.

SELECT MAX(salary) AS max_salary FROM employees;

3. Counts the total number of employees.

SELECT COUNT * AS num_employees FROM employees;

4.Calculates the total salary expense for the company.

SELECT SUM(salary) AS total_salary_expense FROM employees;

5. Calculates the average salary of employees.

SELECT AVG(salary) AS average_salary FROM employees;
GROUP BY CLAUSE
❖ The GROUP BY clause is used to group identical data into groups of tuple with the help of
some aggregate function. (such as COUNT, SUM, AVG, etc.)
❖ E.g., to find the average salary of employees in each department or the number of
employees who work on each project.
❖ GROUPED BY clause is used with SELECT statement.
❖ In the query GROUPED by clause is placed after the WHERE clause.
Syntax:
SELECT expression1,expression2,……..,Aggregate_Function
FROM table_name
WHERE condition
GROUP BY expression1,expression2,…….., ;
GROUP BY CLAUSE
❖Display Country by Count
GROUP BY CLAUSE
❖ Sales Table

❖Note: The GROUP BY clause specifies the grouping attributes, which should also appear in
the SELECT clause.
ORDER BY AND GROUP BY
❖Together
SUMMARIZING THE SELECT STATEMENT
Having Clause
❖ The HAVING clause is used in SQL queries to filter the results of aggregate functions applied to
groups defined by the GROUP BY clause.
❖ It is similar to the WHERE clause but is used specifically with grouped data.

Syntax:
SELECT column1,….., aggregate_function(expression),
FROM table_name
GROUP BY column1,…., HAVING condition;

E.g.,: SELECT Country, COUNT(*) FROM Customer GROUP BY Country HAVING COUNT (*) >= 2
SUMMARIZING THE SELECT STATEMENT
Having Clause
SUMMARIZING THE SELECT STATEMENT
SQL AND, OR and NOT Operators
❖ The WHERE clause can be combined with AND, OR and NOT operators.
❖ These operators are used to filter records based on more than one condition.

❖Syntax: SELECT column1,…. FROM tablename WHERE condition1 AND condition2 …..

SELECT column1,….. FROM tablename WHERE condition1 OR condition2 …..

SELECT column1,….. FROM tablename WHERE NOT condition

SUMMARIZING THE SELECT STATEMENT
SQL LIKE Operator
❖The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
❖There are two wildcards often used in conjunction with the LIKE operator:
% - represents zero, one or multiple characters
_ - represents a single character

❖Syntax:
SELECT column1, column2…. FROM tablename WHRERE column LIKE pattern
SUMMARIZING THE SELECT STATEMENT
SQL LIKE Operator
SUMMARIZING THE SELECT STATEMENT
SQL IN Operator
❖ The IN operator allows to specify multiple values in a WHERE clause.
❖ The IN Operator is shorthand for multiple OR conditions
Syntax:
SELECT column1…. FROM tablename WHERE columnname IN (value1, value2, ….)

Note: NOT IN can also be used.

SUMMARIZING THE SELECT STATEMENT
SQL BETWEEN Operator
❖ The BETWEEN operator selects values within a given range.
❖ The BETWEEN operator is inclusive: begin and end values are included.

Syntax:
SELECT column…. FROM tablename WHERE columnname BETWEEN value1 AND value2
RETRIEVING DATA FROM DIFFERENT TABLES
USING: INNER JOINS
❖ In SQL (Structured Query Language), the JOIN operation is used to combine rows from two
or more tables based on a related column between them.

Inner Join
❖ Inner join produces only the set of records that match in both Table A and Table B.

Syntax:
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
RETRIEVING DATA FROM DIFFERENT TABLES
USING: INNER JOINS
Suppose two table A and table B
RETRIEVING DATA FROM DIFFERENT TABLES
USING: INNER JOINS
Query: SELECT * FROM TableA INNER JOIN TableB ON TableA.PK = TableB.PK
RETRIEVING DATA FROM DIFFERENT TABLES
USING: INNER JOINS
Customers Table Orders Table

Query: SELECT Customers.first_name, Customers.last_name, Orders.order_id,

Orders.order_date
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;
RETRIEVING DATA FROM DIFFERENT TABLES
USING: INNER JOINS
Result:
RETRIEVING DATA FROM DIFFERENT TABLES
USING: OUTER JOIN
❖ An outer join is a type of SQL join that retrieves records from one or more tables even if there is no
corresponding match in the other table.
❖ It includes all the rows from one table, along with matching rows from the other table(s) based on
the specified condition, and fills in any missing values with NULL.

Types of Outer joins are:

• Left (Outer) Join
• Right (Outer) Join
• (Full) Outer Join
OUTER JOIN
Left Outer Join
❖ Left outer join produces a complete set of records from Table A, with the matching records
(where available) in Table B.
❖ If there is no match, the right side will contain null.

Syntax:
SELECT column_name,..,
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

NOTE: LEFT JOIN can also be used.

OUTER JOIN
Left Outer Join
❖Syntax: SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.PK = TableB.PK
OUTER JOIN
Right Outer Join
❖ Right outer join produces a complete set of records from Table B, with the matching records
(where available) in Table A.
❖ If there is no match, the left side will contain null.

Syntax:
SELECT column_name,…
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
OUTER JOIN
Right Outer Join
❖Syntax: SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.PK = TableB.PK
OUTER JOIN
Full Outer Join
❖ Full outer join produces the set of all records in Table A and Table B, with matching records
from both sides where available.
❖ If there is no match, the missing side will contain null.

Syntax:
SELECT column_name
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
OUTER JOIN
Full Outer Join
❖Syntax: SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.PK = TableB.PK
CROSS JOIN
❖ A cross join is a Cartesian Product join – it is every record in Table A combined with every
record in Table B.

Syntax: SELECT * from TableA CROSS JOIN TableB

NESTED QUERY
❖A nested query in SQL contains a query inside another query.
❖The outer query will use the result of the inner query.

SELECT name FROM Employees WHERE

department_id = (SELECT id FROM Departments
WHERE name = 'HR');

https://www.scaler.com/topics/nested-sql-query/
NESTED QUERIES
Two different types:
i. Independent Nested Queries
ii. Co-related Nested Queries

Independent Nested Queries:

❖ In independent nested queries, the execution order is from the innermost query to the outer query.
❖ An outer query won't be executed until its inner query completes its execution.
NESTED QUERIES
Independent Nested Queries:
❖ Operators such as IN, NOT IN, ALL, and ANY are used to write independent nested queries.
NESTED QUERIES
❖ IN Operator: Select all employees who won an award.

SELECT id, name FROM Employees

WHERE id IN (SELECT employee_id FROM Awards);

❖ NOT IN Operator
SELECT id, name FROM Employees
WHERE id NOT IN (SELECT employee_id FROM Awards);
NESTED QUERIES
❖ ANY and ALL operators: used in SQL to compare a value to a set of values returned by a
subquery.
SELECT * FROM Employees WHERE role = 'Developer'
AND salary > ALL (
SELECT salary FROM Employees WHERE role = 'Manager’
);

SELECT * FROM Employees WHERE role = 'Developer'

AND salary > ANY (
SELECT salary FROM Employees WHERE role = 'Manager'
);
NESTED QUERIES
SELECT S_NAME FROM STUDENT
WHERE SCORE > ANY (
SELECT SCORE FROM STUDENT_COURSE WHERE C_ID = 'C1’ );
NESTED QUERIES
Correlated Nested Queries:
In correlated nested queries, the inner query uses values from the outer query.
CREATING AND ALTERING VIEW
❖ It is not always desirable for all users to see the entire set of relations in the database.
❖ May wish to create a personalized collection of “virtual” relations that is better matched to a certain
user's intuition of the structure of the enterprise.

❖Consider a clerk who needs to know an instructor's ID, name, and department name, but does not
have authorization to see the instructor's salary amount.

create view v as <query expression>;

where <query expression> is any legal query expression. The view name is
represented by v.
CREATING AND ALTERING VIEW
❖ A view is a logical representation of another table or combination of tables.
❖ The content of view is computed when it is used with in an SQL statements.
❖ Views is the results of a select statement over other views and base relations.
Syntax:
CREATE VIEW view_name AS SELECT Columns
FROM tables
[WHERE condition];

DROP views:
DROP VIEW view_name;
CREATING AND ALTERING VIEW
❖ Suppose a student table
CREATING AND ALTERING VIEW
CREATE VIEW student_view AS SELECT sname ,sid
FROM students;
//Optional where condition [WHERE condition];

❖ Create a view of name student_view that contain only those student name and age of the
student relation of age greater than 24.

CREATE VIEW student_view AS SELECT sname,age

FROM student
WHERE age>24;
CREATING AND ALTERING VIEW
Altering a View
Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, ...
FROM table_name
WHERE condition;

E.g.,
CREATE OR REPLACE VIEW student_view AS SELECT sname, age, department_id
FROM student
WHERE age>24;
END OF UNIT 6 Dipesh Koirala