SQL queries

Write SQL query to find the name of manufacturers who make more than one beer

select manf from Beers b where exists (select name from Beers b1 where b.name != b1.name and b.manf = b1.manf);

Write SQL query to find the name of manufacturers who make only one beer

select manf from Beers b where not exists (select name from Beers b1 where b.name != b1.name and b.manf = b1.manf);

Write SQL query to find the names of the beer that are liked by people but not Bill

select name from Beers where name != (select beer from Likes where drinker = 'Bill');

Write SQL query to find the name of the beer that Bill likes

select name from Beers where name = (select beer from Likes where drinker = 'Bill');

Write SQL query to find the names of the beer that drinkers like using a subquery

select name from Beers where name in (select beer from Likes);

Write SQL query to find the name of a beer that no drinker likes using a subquery

select name from Beers where name not in (select beer from Likes);

Set the value of the City columns to 'Oslo', but only the ones where the Country column has the value "Norway".

update Customers set city = 'Oslo' where country = 'Norway';

Update the City value to 'Oslo' and the Country value to 'Norway' only for customer with ID (customerID) 32

update Customers set city = 'Oslo', country = 'Norway' where CustomerID = 32;

Update the City column of all records in the Customers table to 'Oslo'

update Customers set city = 'Oslo';

If table S(a,b) has the following values: {(1,3),(1,4),(3,5),(1,3)}, what would be the output of (select * from S) union (select * from S);

{(1,3),(1,4),(3,5)}

For tables R(a int, b int) and S(c int, d int), can you do a natural join?

No

Delete the column Birthday from the Persons table.

alter table Persons drop column Birthday;

Write SQL query to add a column to table R with a unique integer d

alter table R add column d int unique;

Write SQL query to remove column d from table R

alter table R drop column d;

Write SQL query to create an index on price in Sells table called price_idx

create index price_idx on Sells(price);

Write SQL query to make a table Drinkers with 30 characters for name, name is primary key, 50 characters for address, default address "123 Sesame St.", and 16 characters for phone

create table Drinkers( name char(30) primary key, addr char(5) default '123 Sesame St.', phone char(16));

Write SQL query to make a table R with integer a as a primary key, integer b with default value 3, integer c

create table R(a int primary key, b int default 3, c int);

Write SQL query to make a table S with char(4) a as a primary key

create table S(a char(4) primary key);

Write SQL query to make a table S with int a as a primary key

create table S(a int primary key);

Write SQL query to make a table S which has primary key integer a, and a is foreign key that points to R(a). If parent is updated, update child's

create table S(a int primary key, foreign key (a) references R(a) on update cascade;

Write SQL query to make a table S with a int, b int, and (a,b) as a primary key

create table S(a int, b int, primary key (a,b));

Given table R(a int primary key) exists, write SQL query to make a table S which is a foreign key to R(a)

create table S(a int, foreign key (a) references R(a));

Write SQL query to make a table T with age as a tinyint unsigned and age must be at most 150

create table T(age tinyint unsigned check (age <= 150));

Write SQL query to create a view, inner_join, on R(a,b) and S(a,c)

create view inner_join as select * from R natural join S;

Write SQL query to create a view, left_dangling, on R(a,b) and S(a,c) - knowing that the left dangling tuple is (2,3,null)

create view left_dangling as select a,b,c from R natural left outer join S where c is null;

Write SQL query to create a view, left_outer, on R(a,b) and S(a,c)

create view left_outer as select a,b,c from R natural left outer join S;

Write SQL query to create a view, right_dangling, on R(a,b) and S(a,c) - knowing that the right dangling tuple is (3,null,5)

create view right_dangling as select a,b,c from R natural right outer join S where b is null;

Delete all the records from the Customers table where the Country value is 'Norway'

delete from Customers where country = 'Norway';

Write SQL query to remove rows where c is null from table S

delete from S where c is null;

Write SQL query to delete view full_outer

drop view full_outer;

SQL lingo for "if parent foreign key is deleted, also delete the child's

on delete cascade

SQL lingo for "if parent foreign key is deleted, set child's to null"

on delete set null

SQL lingo for "if parent foreign key is deleted, set child's to null and if parent foreign key is updated, also update child's"

on delete set null on update cascade

SQL lingo for "if parent foreign key is updated, also update the child's

on update cascade

SQL lingo for "if parent foreign key is updated, set child's to null"

on update set null

Knowing that there is no drinker who likes to drink Budweiser, write SQL query to find this dangling row in the join of Beers and Likes tables

select * from Beers left outer join Likes on Beers.name = Likes.beer where drinker is null;

Write SQL query to left join tables Beers(name, manf) and Likes (drinker, beer)

select * from Beers left outer join Likes on Beers.name = Likes.beer;

Select all records from the Customers table, sort the result alphabetically by the column City

select * from Customers order by city;

Select all records from the Customers table, sort the result alphabetically, first by the column Country, then, by the column City

select * from Customers order by country, city;

Select all records in the Customers table where the City column has the value 'Berlin' or 'London'

select * from Customers where city = 'Berlin' or city = 'London';

Select all records in the Customers table where the City column has the value "Berlin"

select * from Customers where city = 'Berlin';

Use the NOT keyword to select all records in the Customers table where City is NOT "Berlin"

select * from Customers where not city = 'Berlin';

Use the BETWEEN operator to select all the records in Products where the value of the Price column is between 10 and 20.

select * from Products where price between 10 and 20;

Write SQL query for natural join on tables R and S

select * from R natural join S;

Write SQL query to find the most expensive beer recall Sells(bar, beer, price) table

select beer from Sells where price = (select max(price from Sells);

Write SQL query to print '|a|' where a is the value of column a in table R

select concat('|', a, '|') from R;

Select all the different values from the Country column in the Customers table

select distinct country from Customers;

Given views inner_join, left_dangling, and right_dangling all exist, write SQL query for full outer join

(select * from inner_join) union all (select * from left_dangling) union all (select * from right_dangling);

Delete all the records from the Customers table

DELETE FROM Customers;

If the Sells(bar, beer, price) table has the following prices currently listed: 5,3,4,4,3,null,3,2, What would be the result of select beer from Sells where price >= all (select price from Sells);

Empty set

What is the output of: select beer from Sells where price >= any (select price from Sells);

Finds all beers except those that have a null price

Select all records where the first letter of the City is NOT an "a" or a "c" or an "f"

SELECT * FROM Customers WHERE City LIKE '[!acf]%';

Select all records where the first letter of the City is an "a" or a "c" or an "s"

SELECT * FROM Customers WHERE City LIKE '[acs]%';

Use the IN operator to select all the records where Country is either "Norway" or "France".

SELECT * FROM Customers where country in ('Norway', 'France');

For table R(a int, b int), what is the SQL command to change values of a=2 to 3?

UPDATE R SET a=3 WHERE a=2;

For tables R(a int, b int) and S(a int, c int), can you do a natural join?

Yes