SQL练习

 

SELECT distinct employ_name,city //distinct此时对联和字段（name,city）起作用，而不是单独对name起作用，//错误用法：SELECT name,distinct city
FROM employee natural join works
where company_name='First Bank Corperation'

SELECT distinct name,street,city
FROM employee natural join works
WHERE salary>10000 and company_name='First Bank Company'

SELECT employee_name
FROM works
WHERE not in (SELECT employee_name
              FROM works_
              WHERE company_name='First Bank Company'

//或者

SELECT employee_name
FROM works
EXCEPT
SELECT employee_name
FROM works
WHERE company_name='First Bank Company'

SELECT employee_name 
FROM works
WHERE salary>all(SELECT distinct salary
                 FROM works
                 WHERE company='Small Bank Corporation')

 

SELECT company_name
FROM company
WHERE not exist(
               SELECT distinct city
               FROM company as t
               WHERE t.company_name='Small Bank Corporation'
               EXCEPT
               SELECT distinct city
               FROM company as c
               WHERE cpmpany.company_name=c.company_name
               )

SELECT company_name
FROM(
     SELECT company_name,count(employee_name)
     FROM works
     Group by company_name) as T(company_name,c_em)
WHERE T.c_em=max(T.c_em)//错误，where子句中不能使用聚集函数

//更正
//f
SELECT company_name, count(employee_name) AS c_em
FROM works
GROUP BY company_name
HAVING count(employee_name)=(
  SELECT MAX(cnt)
  FROM (
    SELECT count(employee_name) AS cnt
    FROM works
    GROUP BY company_name
  ) AS sub
);

//g
SELECT company_name,avg(salary) AS a_sa
FROM works
GROUP BY company_name
having avg(salary)>(SELECT avg(salary)
                    FROM works
                    WHERE company_name='First Bank Corporation' )


//其他
SELECT avg(salary) //出现在select的要么是用于分组的元素，要么是聚集函数的元素
FROM works
GROUP BY company_name