MySQL基础练习(二)

CREATE TABLE courses(
        student VARCHAR(10) NOT NULL,
        class VARCHAR(10) NOT NULL
);

INSERT INTO courses VALUES ("A","Math"),
                        ("B","English"),
                        ("C","Math"),
                        ("D","Biology"),
                        ("E","Math"),
                        ("F","Computer"),
                        ("G","Math"),
                        ("H","Math"),
                        ("I","Math"),
                        ("A","Math");

SELECT class FROM courses GROUP BY(class) HAVING COUNT(student)>=5;

CREATE TABLE salary(id INT PRIMARY KEY,
         NAME VARCHAR(10) NOT NULL,
         sex VARCHAR(10) NOT NULL,
         salary INT) NOT NULL
);

INSERT INTO salary VALUES (1,"A","m",2500),
            (2,"B","f",1500),
            (3,"C","m",5500),
            (4,"D","f",500);

UPDATE salary 
SET 
         sex=
         CASE sex
         WHEN ‘m‘
         THEN ‘f‘
         ELSE ‘m‘
         END;         

select person.FirstName,person.LastName,address.City,address.State
from person left join address on person.PersonID=address.PersonID;

delete t1 from email t1, email t2 where t1.Email=t2.Email AND t1.Id>t2.Id;