深入浅出SQL（代码）

USE 

CREATE TABLE doughnut_list
(doughtnut_name VARCHAR(10),
doughtnut_type VARCHAR(6));

CREATE TABLE my_contacts(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(20),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100));

INSERT INTO my_contacts(
last_name,first_name,email,gender,birthday,profession,
location,status,interests,seeking)
VALUES
('Anderson','Jillian','jill_anderson@breakneckpizza.com',
'F','1980-09-05','Technical writer','Palo Alto, CA','Single','Kayaking, Reptitles',
'Relationship Friends');

INSERT INTO doughtnut_purchases(
doughtnut_type,dozens,topping,price)
VALUES
('jelly',3,'sprinkles',3.50);

INSERT INTO my_contacts
(first_name,email,profession,location)
VALUES
('Pat','patpost@breakneckpizza.com','PostalWorker',
'Princeton,NJ');

SELECT * FROM my_contacts;

DROP TABLE doughnut_list;

CREATE TABLE doughnut_list
(
doughtnut_name VARCHAR(10) NOT NULL,
doughtnut_type VARCHAR(6) NOT NULL,
doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00
);

SELECT * FROM my_contacts WHERE first_name = 'Anne';

CREATE DATABASE drinks;
USE drinks;
CREATE TABLE easy_drinks
(
drink_name VARCHAR(25) NOT NULL,
main VARCHAR(25) NOT NULL,
amount1 FLOAT NOT NULL,
second VARCHAR(25) NOT NULL,
amount2 FLOAT NOT NULL,
directions BLOB 
);



INSERT INTO easy_drinks
(
drink_name,main,amount1,second,amount2,directions
)
VALUES
(
'Blackthorn','tonic water',1.5,'pineaple juice',1,
'stir with ice, strain into cocktail glass with lemon twist'
),
('Blue Moon','soda',1.5,'blueberry juice',0.75,'stir with ice, strain into cocktail glass with lemon twist'),
('Oh MY Gosh','peach nectar',1,'pineaple juice',1,'stir with ice, strain into shot glass'),
('Lime Fizz','Sprite',1.5,'lime juice',0.75,'stir with ice, strain into cocktail glass'),
('Kiss on the Lips','cherry juice',2,'apricot nectar',7,'serve over ice with straw'),
('Hot Gold','peach nectar',3,'orange juice',6,'pour hot orange juice in mug and add pach nectar'),
('Lone Tree','soda',1.5,'cherry juice',0.75,'stir with ice, strain into cocktail glass'),
('Greyhound','soda',1.5,'grapefruit juice',5,'serve over ice,stir well'),
('India Summer','apple juice',2,'hot tea',6,'add juice to mug and top off with hot tea'),
('Bull Frog','iced tea',1.5,'lemonade',5,'serve over ice with lime slice');

SELECT * FROM easy_drinks WHERE main = 'Sprite';

SELECT * FROM easy_drinks WHERE main = soda;

SELECT * FROM easy_drinks WHERE amount2 = 6;

SELECT * FROM easy_drinks WHERE second = 'orange juice';

SELECT * FROM easy_drinks WHERE amount1 < 1.5;

SELECT * FROM easy_drinks WHERE amount2 < '1';

SELECT * FROM easy_drinks WHERE main > 'soda';

SELECT * FROM easy_drinks WHERE amount1 = '1.5';

INSERT INTO my_contacts VALUES
('Funyon','Steve','steve@onionflavoreddrings.com','M','1970-04-01','Punk','Grover\'s Mill,NJ',
'Single','smashing the state','compatriots, guitar players');

SELECT * FROM my_contacts WHERE location = 'Grover\'s Mill,NJ';

SELECT drink_name, main, second FROM easy_drinks WHERE main = 'soda';

SELECT drink_name FROM easy_drinks WHERE main = 'cherry juice';

SELECT drink_name FROM easy_drinks WHERE second = 'apricot nectar';

SELECT drink_name FROM easy_drinks WHERE main = 'soda' AND amount1 = 1.5;

SELECT drink_name FROM easy_drinks WHERE main = 'soda' AND amount1 > 1;

CREATE TABLE drink_info(
drink_name VARCHAR(25) NOT NULL,
cost FLOAT,
carbs FLOAT,
colors VARCHAR(15),
ice CHAR(1),
calories INT
);

INSERT INTO drink_info (
drink_name,cost,carbs,colors,ice,calories
)
VALUES
('Blackthorn',3,8.4,'yellow','y',33),
('Blue Moon',2.5,3.2,'blue','y',12),
('Oh My Gosh',3.5,8.6,'orange','y',35),
('Lime Fizz',2.5,5.4,'green','y',24),
('Kiss on the Lips',5.5,42.5,'purple','y',171),
('Hot Gold',3.2,32.1,'orange','N',135),
('Lone Tree',3.6,4.2,'red','y',17),
('Greyhound',4,14,'yellow','y',50),
('Indian Summer',2.8,7.2,'brown','N',30),
('Bull Frog',2.6,21.5,'tan','y',80),
('Soda and It',3.8,4.7,'red','N',19);

SELECT drink_name FROM drink_info WHERE cost >= 3.5 AND calories < 50;

SELECT cost FROM drink_info WHERE ice = 'y' AND calories > 33 AND  colors = 'yellow';

SELECT drink_name, colors FROM drink_info WHERE carbs < 4 AND ice = 'y';

SELECT cost FROM drink_info WHERE calories >= 80;

SELECT drink_name, colors, ice FROM drink_info WHERE cost = 4 OR cost = 5.5;

SELECT drink_name FROM drink_info WHERE drink_name >= 'L' AND drink_name < 'M';

SELECT drink_name FROM easy_drinks WHERE main='orange juice' OR main='apple juice';


CREATE TABLE doughtnut_rating(
location VARCHAR(25),
time VARCHAR(25),
date VARCHAR(30),
type VARCHAR(25),
rating INT,
comments VARCHAR(30)
);

INSERT INTO doughtnut_rating(location,time,date,type,rating,comments)
VALUES
('Kirispy King','8:50 am','9/27','plain glazed',10,'almost perfect'),
('Duncan\'s Donuts','8:59 am','8/25',NULL,6,'greasy'),
('Starbuzz Coffee','7:35 pm','5/24','cinnamon cake',5,'stale, but tasty'),
('Duncan\'s Donuts','7:03 pm','4/26','jelly',7,'not enough jelly');

SELECT type FROM doughtnut_ratings WHERE location='Kirispy King' AND rating<>6;


insert into black_book(date_name, rating)
values
('Alex','innovative'),
('James','boring'),
('Ian','fabulous'),
('Boris','ho hum'),
('Melvin','plebian'),
('Eric','pathetic'),
('Anthony','delightful'),
('Sammy','pretty good'),
('Ivan','dismal'),
('Vic','ridiculous');

select date_name from black_book where rating in ('innovative','fabulous','delightful',
'pretty good');

CREATE TABLE clown_info(
name VARCHAR(10),
last_seen VARCHAR(30),
appearance BLOB,
activities VARCHAR(35)
);

INSERT INTO clown_info(name,last_seen,appearance,activities)
VALUES
('Elsie','Cherry Hill Senior Center','F, red hair, green dress, huge feet','ballons, little car'),
('Pickles','Jack Green\'s party','M, orange hair, blue suit, huge feet','mime'),
('Snuggles','Ball-Mart','F, yellow shirt, baggy red pants','horn, umbrella'),
('Mr.Hobo','BG Circus','M, cigar, black hair, tiny hat','violin'),
('Clarabelle','Belmont Senior Center','F, pink hair, huge flower, blue dress','yelling, dancing'),
('Scooter','Oakland Hospital','M, blue hair, red suit, huge nose','ballons'),
('Zippo','Millstone Mall','F, orange suit, baggy pants','dancing'),
('Babe','Earl\'s Autos','F, all pink and sparkly','balancing, little car'),
('Banzo',NULL,'M, in drag, polka dotted dress','singing, dancing'),
('Sniffles','Trancy\'s','M, green and purple suit, pointy nose',NULL);

INSERT INTO clown_info(name,last_seen,appearance,activities)
VALUES
('Zippo','Millstone Mall','F, orange suit bassy pants','dancing singing'),
('Snuggles','Ball-Mart','F, yellow shirt, bassy blue pants','horn, umbrella');

INSERT INTO clown_info(name,last_seen,appearance,activities)
VALUES
('Banzo','Dickson Park','M, in drag, polka dotted dress','singing, dancing'),
('Sniffles','Trancy\'s','M, green and purple suit, pointy nose','climbing into tiny car');
('Mr.Hobo','party for Eric Gray','M, cigar, black hair, tiny hat','violin');

SELECT * FROM clown_info WHERE name='Zippo';

DELETE FROM clown_info WHERE activities='dancing';

UPDATE doughtnut_rating SET type='glazed' WHERE type='plain glazed';

UPDATE clown_info SET last_seen='Trancy\'s' WHERE name='Mr.Hobo' AND last_seen='Dickson Park';

UPDATE clown_info SET activities='singing' WHERE name='Zippo';
UPDATE clown_info SET appearance='baggy blue pants' WHERE name='Snuggles';
UPDATE clown_info SET last_seen='Dickson Park' WHERE name='Banzo';
UPDATE clown_info SET activities='climbin into tiny car' WHERE name='Sniffles';
UPDATE clown_info SET last_seen='party for Eric Gray' WHERE name='Mr.Hobo';

UPDATE drink_info SET cost = cost + 1 WHERE drink_name='Blue Moon' OR
drink_name='Oh My Gosh' OR drink_name='Lime Fizz';

CREATE DATABASE sql_practice_table;

CREATE TABLE fish_info(common VARCHAR(30),
species VARCHAR(30), location BLOB, weight VARCHAR(25));

INSERT INTO fish_info(common,species,location,weight)
VALUES
('bass,largemouth','M.salmoides','Montgomery Lake,GA','22 lb 4 oz'),
('walleye','S.vitreus','Old Hickory Lake,TN','25 lb 0 oz'),
('trout,cutthroat','O.Clarki','Pyramid Lake,NV','41 lb 0 oz'),
('perch,yellow','P.Flavescens','Bordentown,NJ','4 lb 3 oz'),
('bluegill','L.Macrochirus','Ketona Lake,AJ','4 lb 12 oz'),
('gar,longnose','L.Osseus','Trinity River,TX','50 lb 5 oz'),
('crappie,white','P.annularis','Enid Dam,MS','5 lb 3 oz'),
('pickerel,grass','E.americanus','Dewart Lake,IN','1 lb 0 oz'),
('goldfish','C.auratus','Lake Hodges,CA','6 lb 10 oz'),
('salmon,chinook','O.Tshawytscha','Kenai River,AK','97 lb 4 oz');

CREATE TABLE fish_records(first_name VARCHAR(20), last_name VARCHAR(20),
common VARCHAR(30), location BLOB, state CHAR(2), weight VARCHAR(25), date VARCHAR(15));

INSERT INTO fish_records (first_name,last_name,common,location,state,weight,date)
VALUES
('George','Perry','bass,largemouth','Montgomery Lake','GA','22 lb 4 oz','6/2/1932'),
('Mabry','Harper','walleye','Old Hickory Lake','TN','25 lb 0 oz','8/2/1960'),
('John','Skimmerhorn','trout,cutthroat','Pyramid Lake','NV','41 lb 0 oz','12/1/1925'),
('C.C.','Abbut','perch,yellow','Bordentown','NJ','4 lb 3 oz','5/1/1865'),
('T.S.','Hudson','bluegill','Ketona Lake','AL','4 lb 12 oz','4/9/1950'),
('Townsend','Miller','gar,longnose','Trinity River','TX','50 lb 5 oz','7/30/1954'),
('Fred','Bright','crappie,white','Enid Dam','MS','5 lb 3 oz','7/31/1957'),
('Mike','Berg','pickerel,grass','Dewart Lake','IN','1 lb 0 oz','6/9/1990'),
('Florenttino','Abena','goldfish','Lake Hodges','CA','6 lb 10 oz','4/17/1996'),
('Les','Anderson','salmon,chinook','Kenai River','AK','97 lb 4 oz','5/17/1985');

SELECT * FROM fish_info WHERE location like '%NJ';

SELECT * FROM fish_records WHERE state='NJ';

CREATE TABLE my_contacts(
contact_id INT NOT NULL AUTO_INCREMENT,	
last_name VARCHAR(30) default NULL,
first_name VARCHAR(20) default NULL,
email VARCHAR(50) default NULL,
gender CHAR(1) default NULL,
birthday DATE default NULL,
profession VARCHAR(50) default NULL,
location VARCHAR(20) default NULL,
status VARCHAR(20) default NULL,
interests VARCHAR(100) default NULL,
seeking VARCHAR(100) default NULL,
PRIMARY KEY (contact_id)
);

CREATE TABLE names(
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20),
last_name VARCHAR(20), 
class VARCHAR(10),
email VARCHAR(30),
PRIMARY KEY (id));

INSERT INTO names(id,first_name,last_name)
VALUES(NULL,'Marcia','Brady');

INSERT INTO names(id,first_name,last_name)
VALUES(1,'Jan','Brady');

INSERT INTO names(id,first_name,last_name)
VALUES(2,'Bobby','Brady');

INSERT INTO names(id,first_name,last_name)
VALUES('Cindy','Brady');

INSERT INTO names(id,first_name,last_name)
VALUES(99,'Peter','Brady');




ALTER TABLE my_contacts ADD COLUMN phone_number INT;

use mysql;#以后数据库都用这个
create table projekts(
number INT(11),
descriptionofproj VARCHAR(50),
contractoronjob VARCHAR(50)
);

INSERT INTO projekts(number,descriptionofproj,contractoronjob)
VALUES
(1,'outside house painting','Murphy'),
(2,'kitchen remodel','Valdez'),
(3,'wood floor installation','Keller'),
(4,'roofing','Jackson');

ALTER TABLE projekts RENAME TO project_list;

ALTER TABLE project_list CHANGE COLUMN number  proj_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (proj_id);

ALTER TABLE project_list CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100),
CHANGE COLUMN contractoronjob con_name VARCHAR(30);

ALTER TABLE project_list ADD COLUMN con_phone VARCHAR(10),
ADD COLUMN start_date DATE,
ADD COLUMN est_cost DECIMAL(7.2);

ALTER TABLE project_list DROP COLUMN start_date;

CREATE TABLE hooptie(
color VARCHAR(10),
year INT(4),
make VARCHAR(15),
mo VARCHAR(15),
howmuch FLOAT(8.3)
);
INSERT INTO hooptie(color,year,make,mo,howmuch)
VALUES
('silver',1998,'Porsche','Boxter',17992.540),
(NULL,2000,'Jaguar','XJ',15995),
('red',2002,'Cadillac','Escalade',40215.9);

ALTER TABLE hooptie RENAME TO car_table;
ALTER TABLE car_table CHANGE COLUMN mo model VARCHAR(15),
CHANGE COLUMN howmuch price FLOAT(8.2);

ALTER TABLE car_table ADD COLUMN car_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (car_id),
ADD COLUMN VIN VARCHAR(30);

ALTER TABLE car_table 
MODIFY COLUMN car_id BEFORE color,
MODIFY COLUMN VIN AFTER car_id,
MODIFY COLUMN make AFTER VIN,
MODIFY COLUMN model AFTER make;

CREATE TABLE movie_table(
movie_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(40),
rating CHAR(1),
drama CHAR(1),
comedy CHAR(1),
action CHAR(1),
gore CHAR(1),
scifi CHAR(1),
for_kids CHAR(1),
cartoon CHAR(1),
purchased VARCHAR(15),
PRIMARY KEY (movie_id)
);

ALTER TABLE movie_table MODIFY COLUMN rating CHAR(2);

INSERT INTO movie_table(movie_id,title,rating,drama,comedy,action,gore,
scifi,for_kids,cartoon,purchased)
VALUES
(1,'Monsters,Inc.','G','F','T','F','F','F','T','T','3-6-2002'),
(2,'The Godfather','R','F','F','T','T','F','F','F','2-5-2001'),
(3,'Gone with the Wind','G','T','F','F','F','F','F','F','11-20-1999'),
(4,'American Pie','R','F','T'.'F','F','F','F','F','4-19-2003'),
(5,'Nightmare on Elm Street','R','F','F','T','T','F','F','F','4-19-2003'),
(6,'Casablanca','PG','T','F','F','F','F','F','F','2-5-2001');

ALTER TABLE movie_table ADD COLUMN category VARCHAR(20);

UPDATE movie_table SET category = 'drama' WHERE drama = 'T';
UPDATE movie_table SET category = 'comedy' WHERE comedy = 'T',
UPDATE movie_table SET category = 'action' WHERE action = 'T',
UPDATE movie_table SET category = 'horror' WHERE gore = 'T',
UPDATE movie_table SET category = 'scifi' WHERE scifi = 'T',
UPDATE movie_table SET category = 'family' WHERE family = 'T',
UPDATE movie_table SET category = 'family' WHERE cartoon = 'T' AND rating = 'G',
UPDATE movie_table SET category = 'misc' WHERE cartoon = 'T'AND rating <> 'G';

UPDATE movie_table SET category = 
CASE
	WHEN drama = 'T' THEN 'drama'
	WHEN comedy = 'T' THEN 'comedy'
	WHEN action = 'T' THEN 'action'
	WHEN gore = 'T' THEN 'horror'
	WHEN scifi = 'T' THEN 'scifi'
	WHEN for_kids = 'T' THEN 'family'
	WHEN cartoon = 'T' THEN 'family'
	ELSE 'misc'
END;	

SELECT title, category FROM movie_table WHERE
 category = 'family' ORDER BY title;


 CREATE TABLE test_chars(chars CHAR(1));
 INSERT INTO TABLE chars(chars)
 VALUES
 ('0'),('1'),('2'),('3'),('A'),('B'),('C'),('D'),('a'),('b'),
 ('c'),('d'),('!'),('@'),('#'),('$'),('%'),('^'),('&'),('*'),
 ('('),(')'),('-'),('_'),('+'),('='),('['),(']'),('{'),('}'),
 (';'),(':'),('''),('"'),('\'),('|'),('~'),(','),('.'),('<'),
 ('>'),('/'),('?');

 SELECT first_name FROM cookie_sales ORDER BY sales DESC;

 SELECT first_name, SUM(sales) FROM cookie_sales GROUP BY first_name
 ORDER BY SUM(sales) DESC;

 SELECT first_name, SUM(sales) FROM cookie_sales GROUP BY first_name
 ORDER BY SUM(sales) DESC LIMIT 1,1;

 SUBSTRING_INDEX(interests, '.', 1)

 ALTER TABLE my_contacts 
 ADD COLUMN interest1,
 ADD COLUMN interest2,
 ADD COLUMN interest3,
 ADD COLUMN interest4;

 INSERT INTO my_contacts(interest1,interest2,interest3,interest4)
 values
 (SUBSTRING_INDEX(interests,'.',1), SUBSTRING_INDEX(interests,'.',2),
 SUBSTRING_INDEX(interests,'.',3), SUBSTRING_INDEX(interests,'.',4));


 SELECT * FROM my_contacts WHERE gender='F'
 AND status='Single' AND state='MA'
 AND seeking='M' AND interest1 ='animals';


 CREATE TABLE interests(
 _id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 interests VARCHAR(50) NOT NULL,
 contact_id INT NOT NULL,
 CONSTRAINT my_contacts_contact_id
 FOREIGN KEY (contact_id)
 REFERENCES my_contacts(contact_id)
 );

 UPDATE my_contacts SET 
 interest1 = SUBSTRING_INDEX(interests, '.', 1),
 interests = SUBSTR(interests, LENGTH(interest1)+2),
 interest2 = SUBSTRING_INDEX(interests, '.', 1),
 interests = SUBSTR(interests, LENGTH(interest2)+2),
 interest3 = SUBSTRING_INDEX(interests, '.', 3),
 interests = SUBSTR(interests, LENGTH(interest3)+2),
 interest4 = interests;

 SELECT interest1,interest2,interest3,interest4 FROM my_contacts;



 CREATE TABLE profession
 (
 	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 	profession VARCHAR(20)
 );
 INSERT INTO profession (profession)
 SELECT profession FROM my_contacts
 GROUP BY profession
 ORDER BY profession;

 CREATE TABLE profession AS SELECT profession FROM my_contacts
 GROUP BY profession
 ORDER BY profession;

 ALTER TABLE profession
 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
 ADD PRIMARY KEY (id);

  CREATE TABLE profession
 (
 	id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 	profession VARCHAR(20)
 ) AS 
 SELECT profession FROM my_contacts
 GROUP BY profession
 ORDER BY profession;


 SELECT boys.boy, toys.toy
 FROM boys
 INNER JOIN toys
 ON boys.toy_id = toys.toy_id;

 SELECT mc_ta.emial, prof.profession
 FROM my_contacts AS mc_ta
 INNER JOIN
 profession AS prof
 ON mc_ta.prof_id = prof.prof_id;

 SELECT mc_ta.first_name, mc_ta.last_name,sta.status
 FROM my_contacts mc_ta
 INNER JOIN
 status sta 
 ON mc_ta.status_id <> sta.status_id;


 SELECT jc.contact_id FROM
 job_current jc
 INNER JOIN
 job_desired jd 
 WHERE jc.contact_id = jd.contact_id AND
 date - jc.start_date >= 5 AND 
 jd.salary_low <= 105000;

 SELECT mc.first_name, mc.last_name, mc.phone, jc.title
 FROM job_current AS jc NATURAL JOIN my_contacts AS mc
 WHERE jc.title IN (SELECT title FROM job_list);

 SELECT mc.first_name, mc.last_name, 
 (SELECT state FROM zip_code
 WHERE mc.zip_code = zip_code) AS state
 FROM my_contacts mc;

 SELECT mc.first_name, mc.last_name,
 (SELECT salary FROM job_current
 WHERE salary=MAX(jc.salary) FROM job_current jc) AS salary
 FROM my_contacts mc;

 SELECT mc.first_name, mc.last_name,jc.salary,
 jc.salary - (SELECT AVG(salary) FROM job_current WHERE title='Web Developer')
 FROM my_contacts mc NATURAL JOIN job_current jc
 WHERE jc.title='Web Developer';

 SELECT mc.first_name, mc.last_name,mc.phone,jc.title
 FROM job_current AS jc NATURAL JOIN my_contacts AS mc
 WHERE jc.title IN (SELECT title FROM job_listings);



 SELECT jc.title FROM job_current jc WHERE jc.salary = (SELECT MAX(salary) FROM job_listings);

 SELECT mc.first_name, mc.last_name FROM my_contacts mc NATURAL JOIN job_current jc
 WHERE jc.salary > (SELECT AVG(salary) FROM job_current);

 SELECT mc.first_name, mc.last_name FROM my_contacts mc 
 NATURAL JOIN job_current jc 
 WHERE jc.title='Web Developer' AND mc.zip_code 
 IN (SELECT zip FROM job_listings WHERE title='Web Developer');

 SELECT mc.first_name, mc.last_name FROM my_contacts mc
 WHERE zip_code IN (SELECT mc.zip_code FROM my_contacts mc
 NATURAL JOIN job_current jc 
 WHERE jc.salary = (SELECT MAX(salary) FROM job_current));



 SELECT mc.first_name, mc.last_name mc.email FROM my_contacts mc
 WHERE NOT EXISTS 
 (SELECT * FROM job_current jc WHERE mc.contact_id=jc.contact_id)
 AND
 1 <= (SELECT COUNT(*) FROM contact_interest WHERE
 contact_id=mc.contact_id);


 SELECT g.girl, t.toy
 FROM girls g 
 LEFT OUTER JOIN toys t 
 ON g.toy_id=t.toy_id;

 SELECT ci1.name, ci2.name AS boss
 FROM clown_info1 ci1 
 LEFT OUTER JOIN
 clown_info2 ci2
 ON ci1.boss_id=ci2.id;

 GRANT SELECT ON clown_info TO Elsie;

 REVOKE SELECT ON clown_info FROM Elsie;

 REVOKE INSERT,DELETE ON location FROM Elsie RESTRICT;