Intermediate SQL QUIZ

QUIZ

 

Query 1: HOW many languages are spoken in the USA?

SELECT CountryCode, COUNT(Language) FROM countrylanguage WHERE CountryCode='USA';

​

Query2: Count how many languages are in the world

SELECT COUNT(DISTINCT Language) AS languages FROM countrylanguage;

 

 

INTERMEDIATE DDL

MySQL constraints

UNIQUE

CHECK

DEFAULT

​

DEFAULT는 title VARCHAR(45) DEFAULT '사원'

UNIQUE는 CONSTRAINT UP_empname UNIQUE(empname)

CHECK는 CONSTRAINT CH_salaray CHECK (Salary<600000)

​

 

 

 

 

 

Intermediate DML

GROUP BY

HAVING

ORDER BY

LIMIT

​

​

GROUP BY

User to group tuples with the ★same value ★ in the attribute

★GROUP BY with aggregation functions★

 

 

 

 

 

 

 

 

Query 3: How many countries speak each lagnuage

SELECT Language,COUNT(Language) FROM countrylanguage

GROUP BY Language;

​

​

​

HAVING IS SET BY GROUP BY

GROUP BY가 있어야 HAVING이 있을 수 있다

HAVING IS A COUNDITION ON A GROUP OF TUPLES

 

 

 

 

 

 

 

 

SELECT dno, AVG(salary) as avgsal, MAX(salary) as maxsal

FROM employee

GROUP BY dno

HAVING avgsal >= 2500000;

​

​

​

ORDER BY

정렬

ORDER BY Attribute ASC;

ORDER BY Attribute DESC;

​

★시험에 나오는거

ASC으로 정렬하면 NULL값이 처음에 나오고

DESC으로 정렬하면 NULL 값이 마지막에 나온다

유남쌩?

 

 

 

 

 

SELECT salary, title, empname

FROM employee

WHERE dno = 2

ORDER BY salary;

 

 

 

 

 

 

 

Query 4: Find how many countries speak each language ( with percentage>50%)

and output them in decreasing order

​

SELECT Language, COUNT(CountryCode) FROM countrylanguage

WHERE Percentage > 50

GROUP BY Language

ORDER BY COUNT(Language) DESC;

 

 

 

 

 

 

Query 5: Find languages that are spoken in at least 3 different

countries with percentage at least 50

SELECT Language, COUNT(CountryCode) FROM countrylanguage

WHERE Percentage >=50

GROUP BY Language

HAVING COUNT(CountryCode) >=3 ;

​

​

LIMIT

수 제한

SELECT * FROM employee LIMIT 3;