Advanced Queries

Referential Actions

ON DELETE CASCADE

ON DELETE NO ACTION

ON DELETE RESTRICT

ON DELETE SET NULL

ON DELETE DEFAULT

 

 

 

ON DELETE CASCADE

 

 

 

 

ON DELETE SET NULL

 

 

 

Referential Actions은 referencing하는 테이블에서 건다

 

CREATE TABLE employee(

empno int NOT NULL,

empname varchar(45),

title varchar(45) DEFAULT '사원',

manager int,

salary int,

dno int,

CONSTRAINT UQ_empname UNIQUE (empname),

CONSTRAINT CH_salary CHECK (salary < 6000000),

CONSTRAINT CH_dno CHECK (dno IN (1, 2, 3, 4, 5, 6)),

CONSTRAINT PK_Employee PRIMARY KEY (empno),

CONSTRAINT FK_Employee_Manager

FOREIGN KEY (manager) REFERENCES employee(empno),

CONSTRAINT FK_Department_Employee

FOREIGN KEY (dno) REFERENCES department(deptno) ON DELETE CASCADE

);

예시

employee의 dno가 department의 deptno를 referencing한다

여기다가 referential action을 쓴다

department의 detpno에 쓰진않음

 

 

 

Join Query

SELECT *

FROM employee, department

WHERE department.deptno = employee.dno;

 

 

 

 

 

 

Join Query(Example)

 

 

Q1: Show the countries and their cities

SELECT C.Name, T.Name

FROM Country AS C, City AS T

WHERE C.Code=T.CountryCode;

​

​

​

Q2: Show the countries located in Europe and their cities

SELECT C.Name, T.Name

FROM Country AS C, City AS T

WHERE C.Code=T.CountryCode AND C.Continent='Europe';

​

​

Q3: Show countries that speak at least 50% in Korean

SELECT C.Name, CL.Percentage

FROM Country AS C, CountryLanguage AS CL

WHERE C.Code=CL.CountryCode AND CL.Percentage>=50

AND CL.Language='Korean';

 

 

 

 

 

 

1. Find the most popular official languages in the world
2. Output result in a descending order of popularity
3. Report the 5 most popular ones

​

SELECT Language, COUNT(CountryCode)

FROM countrylanguage

WHERE IsOfficial='T'

GROUP BY Language

ORDER BY COUNT(CountryCode) DESC

LIMIT 5;

​

​

​

​

Nested Query

When a scalar value(single value) is returned

Q1: Countries that have the same independence year with South Korea

SELECT Name, IndepYear

FROM Country

WHERE IndepYear =

(SELECT IndepYear

FROM Country

WHERE Name = 'South Korea');

​

​

​

Q2: Where does a city called Berlin located (Use NESTED query)?

SELECT C.Name

FROM Country C

WHERE C.code =

(SELECT T.CountryCode

FROM City T

WHERE T.name = 'Berlin');

​

​

Q3: Show which countries are between China and Japan in terms of population. Order the result by population in descending order

​

​

​

When a relation with one attribute is returned

 

 

 

 

 

4: Find countries in Asia that have cities with population of more than 5 million

SELECT C.Name

FROM Country C

WHERE C.Continent = 'Asia'

AND C.Code IN (SELECT T.CountryCode

FROM City T

WHERE T.Population > 5000000);

 

 

 

 

 

Q5: Show the name and population of the country with the max population in Asia.

SELECT Name, Population

FROM Country

WHERE Continent='Asia'

ORDER BY Population DESC

LIMIT 1;

​

​

Nested Query – When a relation with multiple attribute is returned