Skip to content

Latest commit

 

History

History
49 lines (42 loc) · 1.04 KB

SQL Practice 2.md

File metadata and controls

49 lines (42 loc) · 1.04 KB
  1. create clinet table
CREATE TABLE Client
(
    cid CHAR(20) NOT NULL,
    cname CHAR(10) NOT NULL,
    age REAL,
    grade CHAR(10) NOT NULL,
    job CHAR(20),
    mileage INTEGER,
    primary key (cid)
);
  1. create Kim's VIEW and get ages of Kim's
CREATE VIEW KIM_VIEW(cid, cname, age, grade, job, mileage)
AS SELECT *
FROM Client C
WHERE C.name LIKE '김%';
SELECT K.age FROM KIM_VIEW K;
  1. get number of people by grade
SELECT * FROM Client;
SELECT C.grade, COUNT (*) AS gcnt
FROM Client C
GROUP BY C.grade;
  1. get grade that has minimumm average ages

~~SELECT C.grade
FROM Client C
GROUP BY C.grade
HAVING AVG (C.age)
= (SELECT MIN (AVG (C2.age)) AS mgavg FROM Client C2 GROUP BY C2.grade);

마지막 그룹바이 안 해도 되는지 체크 필요.
급해서 위와 같이 작성 이후 제출함 ~~


틀렸다.

get minimum average age's rating

이게 올바른 답이다.