SQLの練習帳

sql_practice_20130928.sql

UPDATE class_A SET sex_code =1;

SELECT * FROM class_A;

UPDATE class_A SET sex_code = 2 WHERE id IN (3,4);

-- sex_codeをわかりやすく出力する
SELECT name,
CASE sex_code
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
ELSE 'Unknown' END
FROM class_A;

-- 男性と女性の数を計算する
SELECT
  SUM(CASE WHEN sex_code = 1 THEN 1 ELSE 0 END) As males,
  SUM(CASE WHEN sex_code = 2 THEN 1 ELSE 0 END) As Females 
FROM class_A;

CREATE TABLE Personnel
(emp_name VARCHAR(32),
birth_date DATE,
PRIMARY KEY (emp_name));

INSERT INTO Personnel VALUES ('Ken', '1989-01-01'),
  						 ('Smith', '1970-02-05'),
							 ('Dave', '1965-03-10');
							 
CREATE TABLE Celebrities
(celeb_name VARCHAR(32),
birth_date DATE,
birth_city_name VARCHAR(32),
PRIMARY KEY (celeb_name));

INSERT INTO Celebrities VALUES
	('Lenon', '1989-01-01', 'Tokyo'),
	('Paul', '1950-09-20', 'Los Agel'),
	('Glamor', NULL, 'New York');
	
SELECT P.emp_name As famous_person_birth_date_guy
  FROM Personnel As P
  WHERE NOT EXISTS 
  	(SELECT *
  		FROM Celebrities As C
  		WHERE P.birth_date = C.birth_date);
  		
SELECT P.emp_name
  FROM Personnel As P
  WHERE P.birth_date NOT IN
  (SELECT C.birth_date FROM Celebrities As C
   WHERE C.birth_city_name = 'New York');
   
SELECT P.emp_name
  FROM Personnel As P
  WHERE NOT EXISTS
    (SELECT * FROM Celebrities As C
     WHERE C.birth_city_name = 'New York'
     AND C.birth_date = P.birth_date);
     
SELECT P.emp_name
  FROM Personnel As P, Celebrities As C
  WHERE P.birth_date = C.birth_date;
  
SELECT P.*,C.*
  FROM Personnel As P
  	LEFT OUTER JOIN 
  	  Celebrities As C
  ON C.birth_date = P.birth_date
  WHERE C.celeb_name IS NULL;

CREATE TABLE Personnel
(emp_nbr  INTEGER NOT NULL,
 dept_nbr INTEGER NOT NULL,
 job_title CHAR(16)
);

TRUNCATE Personnel;
INSERT INTO Personnel VALUES
(100, 1, 'Programmer'),
(101, 1, 'Programmer'),
(102, 1, 'Programmer'),
(103, 1, 'Sales'),
(104, 1, 'Sales'),
(105, 1, 'Sales'),
(106, 1, 'Sales'),
(107, 2, 'Sales'),
(108, 2, 'Sales'),
(108, 2, 'Engineer'),
(108, 2, 'Engineer'),
(108, 2, 'Engineer'),
(108, 2, 'Engineer'),
(108, 2, 'Engineer');

SELECT dept_nbr 
  FROM Personnel
  WHERE job_title = 'Programmer'
  GROUP BY dept_nbr
HAVING COUNT(*) < 5;

SELECT DISTINCT dept_nbr
  FROM Personnel As P1
  WHERE 5 > (SELECT COUNT(DISTINCT P2.emp_nbr)
               FROM Personnel As P2
              WHERE P1.dept_nbr = P2.dept_nbr
                AND P2.job_title = 'Programmer');

SELECT dept_nbr
  FROM Personnel
  GROUP BY dept_nbr
  HAVING SUM(CASE WHEN job_title = 'Programmer'
  				  THEN 1 ELSE 0 END) < 5;
  				  
SELECT COUNT(DISTINCT P1.emp_nbr)
  FROM Personnel As P1, Personnel As P2
  WHERE P1.dept_nbr = P2.dept_nbr
  AND P1.job_title = 'Programmer';
  
  SELECT DISTINCT dept_nbr
  FROM Personnel As P1
  WHERE 5 > 3;

CREATE TABLE Students
(stud_nbr INTEGER NOT NULL,
stud_name VARCHAR(32) NOT NULL,
sex_code INTEGER,
stud_age INTEGER,
PRIMARY KEY(stud_nbr));

INSERT INTO Students VALUES
('1', 'Smith', 1, 16),
('2', 'Smyth', 2, 17),
('3', 'Smoot', 2, 16),
('4', 'Adams', 2, 17),
('5', 'Jones', 1, 16),
('6', 'Celko', 1, 17),
('7', 'Vennor', 2, 16),
('8', 'Murray', 1, 18);

SELECT S1.stud_nbr, S1.stud_name, S1.sex_code, S1.stud_age
  FROM Students As S1
 WHERE stud_age
         < (SELECT MAX(stud_age)
              FROM Students As S2
             WHERE S1.sex_code = S2.sex_code); 
             
CREATE TABLE Suppliers 
(sup_id CHAR(16));

CREATE TABLE SupParts
(sup_id CHAR(16),
part_nbr CHAR(16),
part_qty INTEGER);

INSERT INTO Suppliers VALUES
('S1'),
('S2'),
('S3'),
(null);

INSERT INTO SupParts VALUES
('S1','P1', 100),
('S1','P2', 250),
('S2','P1', 100),
('S2','P2', 250);

SELECT Suppliers.sup_id, SupParts.part_nbr, SupParts.part_qty
  FROM Suppliers
         LEFT OUTER JOIN
           SupParts
    ON Suppliers.sup_id = SupParts.sup_id;
    
CREATE TABLE Credits
(student_nbr INTEGER NOT NULL,
 course_name CHAR(8) NOT NULL,
   PRIMARY KEY (student_nbr, course_name));

INSERT INTO Credits VALUES vvvvv b 
(1, 'CS-101'),
(1, 'CS-102'),
(2, 'CS-101'),
(3, 'CS-102');

SELECT C1.student_nbr, C1.course_name, C2.course_name
  FROM Credits As C1
          LEFT OUTER JOIN
            Credits As C2
    ON C1.student_nbr = C2.student_nbr
   AND C1.course_name = 'CS-101'
   AND C2.course_name = 'CS-102'   
 WHERE COALESCE(C1.course_name, 'CS-101') = 'CS-101'
   AND COALESCE(C2.course_name, 'CS-102') = 'CS-102';

SELECT C1.student_nbr, C1.course_name, C2.course_name
  FROM Credits As C1
          LEFT OUTER JOIN
            Credits As C2
    ON C1.student_nbr = C2.student_nbr
   AND C1.course_name = 'CS-101'
   AND C2.course_name = 'CS-102'   
 WHERE (CASE WHEN C1.course_name IS NOT NULL 
             THEN C1.course_name
             ELSE 'CS-101' 
         END ) = 'CS-101'
   AND (CASE WHEN C2.course_name IS NOT NULL
   			 THEN C2.course_name
   			 ELSE 'CS-102' 
   	     END ) = 'CS-102';