Structured Quey Language2-Database Systems

Structured Quey language II
Database systems
M.Y Abdur Rahman
 Group by
Statements  Order by
 View
 Join
Sub program  SQL function
Sub routine  Stored procedure

 Trigger
 Group by operator logically rearrange the relation into groups,
such as to group all the rows that has the same value.
 Statement groups rows that have the same values into summary
rows. ( COUNT() , MAX() , MIN() , SUM() , AVG() ) E.g.: find the
number of customers in each country.
Grouping SELECT attribute

FROM relation
WHERE condition(s)
GROUPED BY attribute
ORDER BY attribute(s) – ASC or DESC
 Create a database called Loan and the following tables with
relevant data.
Part
part_no part_name colour weight city
(pk)
Project
Questions project_no project_name city
(pk)
Shipment
shipment_no quantity part_no project_no
(pk) (fk) (fk)

 create database loan;
 use loan;
 create table part(part_no int not null primary key,part_name
varchar(30),colour varchar(20),weight int,city varchar(30));
Query(answer)  insert into part(part_no,part_name,colour,weight,city)

values(1,'part1','red',40,'Gampaha'),(2,'part2','green',70,'Kandy'),
(3,'part3','red',30,'Gampaha'),(4,'part4','blue',50,'Kalutara’);
 create table project(project_no int not null primary
key,project_name varchar(30),city varchar(30));
 insert into project(project_no,project_name,city)
values(1,'proj1','Gampaha'),(2,'proj2','Kalutara'),(3,'proj3','Kandy’);
 create table shipment(shipment_no int not null primary
key,quantity int,part_no int,project_no int,foreign key(part_no)
references part(part_no),foreign key(project_no) references
project(project_no));
 alter table shipment drop primary key;
Query  create table shipment(shipment_no int,quantity int,part_no

int,project_no int,foreign key(part_no) references
part(part_no),foreign key(project_no) references
project(project_no));
 insert into shipment(shipment_no,quantity,part_no,project_no)
values(1,1,1,50),(1,1,2,90),(1,2,1,40),(1,3,3,20),(2,1,3,110),
(2,2,2,30),(2,4,3,10),(3,3,1,100),(3,1,3,80),(3,4,2,70),(4,2,1,60),
(4,1,3,20);
 Question 01: find the total quantity supplied by each supplier from
the shipment table.
select shipment_no, sum(quantity) from shipment group by

shipment_no;
 Question 02: Find the minimum quantity for each project and
Answer display in descending order of the project number from shipment
table.
select project_no,min(quantity) from shipment group by

project_no order by project_no DESC;
 Question 03: Count the number of parts supplied by each supplier
and display in ascending order of supplier no.
select shipment_no,count(distinct part_no) from shipment group

by shipment_no order by shipment_no ASC;
 Question 04: Find the average quantity supplied for each project
Answer and display the result in descending order of average quantity.
For this query quantity more than 100 will not be valid.
select project_no, avg(quantity) from shipment where

quantity<=100 group by project_no order by avg(quantity)desc;
 Create the following tables with relevant data.
Employee
Emp_id emp_name Emp_salary Dept_no
(pk) (fk)
Department
Joins Dept_no Dept_name
(pk)
 create table department(dept_no varchar(20) not null primary
key,dept_name varchar(20));
 insert into department(dept_no,dept_name)
values('d01','finance'),('d02','HR'),('d03','IT'),('d04','admin');
 create table employee(emp_id varchar(20) not null primary
key,emp_name varchar(20),emp_salary int,dept_no
Query varchar(20),foreign key(dept_no) references
department(dept_no));
 insert into employee(emp_id,emp_name,emp_salary,dept_no)
values('e01','amal',10000,'d02'),('e02','Kamal',15000,'d02'),
('e03','Saman',12000,'d01'),('e04','Ajith',14000,'d02');
 Question 05: Write a query to list employee name and salary from
the employee table and department number and name from
department table .
select e.emp_name,e.emp_salary,d.dept_no,d.dept_name from
employee e, department d where e.dept_no=d.dept_no;
Answer
 IT is like a virtual table that contain data from one or multiple
table.
 We can also control the user security for accessing data from the
database table.
 Present only the data which user require access
Create view <view name>(attribute)
view AS
Query
 Question 05:create a view called vEmployee having name and city.
create view vemployee(emp_name,emp_salary) as select

emp_name, emp_salary from employee;
select * from vemployee;
Answer Drop view vemployee;
 Question 06:create a view called employee view having employee

id and name department name from department table.
create view employeeview as select e.emp_id, e.emp_name,

d.dept_no from employee e, department d where
e.dept_no=d.dept_no;
 CREATE USER <name> IDENTIFIED BY <password>
Create user Eg: create user Ruwan identified by Ab@2022
 GRANT <Priviledge list> ON <object> TO <subject>

Security & access control  Privileges are select, insert ,delete
commands in SQL- grant Eg: grant select, insert on supplier TO Saman with grand option
Security & access control  REVOKE <Priviledge list> ON <object> FROM <subject>
commands in SQL- revoke
Eg: revoke insert on supplier from Saman
CREATE FUNCTION function_name(@parameter (data type))
RETURNS return_datatype
AS
BEGIN
RETURNS return _datatype
SQL functions END;
https://www.youtube.com/watch?v=MlaAX59l2b4
CREATE PROCEDURE procedure_name(parameter data type)
AS
BEGIN
END;
SQL stored  To execute stored procedure:
 Execute procedure_name;
procedure  Exec procedure_name
 Just procedure_name;
https://www.youtube.com/watch?v=7DoFxbBuqx0
CREATE TRIGGERS trigger_name(parameter data type)
ON
FOR
AS
SQL trigger BEGIN
END;
https://www.youtube.com/watch?v=KLJ29y3qqyk
Stored procedure Trigger
 We can execute a stored  A trigger can only be executed
procedure whenever we want whenever or event(insert,
using the exec command. delete and update) is fired ion
Stored  We can call a stored
the table.
procedure vs procedure from inside

another stored procedure.
 We cannot directly call
another trigger within a
Trigger  Stored procedure can take
trigger.
 We cannot pass parameters as
input parameters.
input to a trigger.
 Stored procedures can return
 A trigger cannot return a
values.
value.
 Stored procedures are used
 The triggers are normally used
for performing tasks.
for auditing work.
 A transaction is a logical unit of database processing which can
include one or more database operations such as insertion,
deletion modification or retrieval operation.(two operation)
 read_item(x): read a database item named x into a program variable
x.
 write_item(x): writes the value of program variable x into the
database item named x.
Transaction (a)T1
(a)T2
read_item(x)
read_item(x)
x:=x-N;
x:=x+M;
write_item (x)
write_item (x)
read_item(y)
y:=y+n;
write_item(y);
 Transfer Rs 5000 from account A to B.
T1
read_item(A)
Exercise A:=A-5000;
write_item(A)
read_item(B)
B:=B+5000;
write_item(B);
END

Structured Quey Language2-Database Systems

Uploaded by

Copyright:

Available Formats

Structured Quey Language2-Database Systems

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Structured Quey Language2-Database Systems

Uploaded by

Copyright:

Available Formats

Structured Quey language II

Sub program  SQL function

Sub routine  Stored procedure

Grouping SELECT attribute

(pk) (fk) (fk)

Query(answer)  insert into part(part_no,part_name,colour,weight,city)

Query  create table shipment(shipment_no int,quantity int,part_no

select shipment_no, sum(quantity) from shipment group by

select project_no,min(quantity) from shipment group by

select shipment_no,count(distinct part_no) from shipment group

select project_no, avg(quantity) from shipment where

create view vemployee(emp_name,emp_salary) as select

select * from vemployee;

Answer Drop view vemployee;

 Question 06:create a view called employee view having employee

create view employeeview as select e.emp_id, e.emp_name,

 GRANT <Priviledge list> ON <object> TO <subject>

procedure vs procedure from inside

You might also like