sql

--select * from properties
--select price from properties
--select * from properties where location = 'Angel'
--select * from properties where agent = 'Duckworth'
--select * from properties where date_sold is null;
--select * from properties order by price;
--select * from properties order by price limit 5;
--select distinct agent from properties;
--select * from properties where bedrooms = 2 and bathrooms = 2;   
--select min(price) from properties;
--select sum(price) from properties
--select avg(price) from properties;
--select min(price) from properties where bedrooms = 2
--select * from properties
--select distinct agent from properties group by agent having avg(sold_price) is null
--select distinct agent, sum(sold_price), avg(sold_price) from properties where sold_price is not null group by agent order by sum(sold_price) desc limit 1;
--select distinct property_type, avg(price) from properties group by property_type
--select distinct location, avg(price) from properties group by location order by avg(price) desc limit 3
--select distinct agent, count(sold_price) from properties group by agent having count(sold_price) > 2
--select * from properties where date_sold > '2017-03-01'
--insert into properties (property_type, bedrooms, bathrooms, location, price, agent) values ('house', 2, 2, 'Angel', 950000, 'toby')
--select * from properties
--insert into properties (property_type, bedrooms, bathrooms, location, price, agent, sold_price) values ('flat', 1, 1, 'Hoxton', 300000, 'toby', 25000)
--update properties set sold_price = 145000, date_sold = '2017-05-05' where id = 12 
--select * from properties where location = 'Dalston' and bedrooms =5
--update  properties set price = 1500000 where location = 'Dalston' and bedrooms = 5
--update properties set date_sold = null, sold_price = null where agent = 'Foxtons'
--update properties set date_sold = '2017-03-28' where location = 'Angel' and bedrooms = 2 and property_type = 'house' and date_sold is not null
--select * from properties where property_type = 'garage' 
--delete from properties where agent = 'Foxtons' 

       
--select distinct agent, (sum(price) - sum(sold_price)) / sum(sold_price), sum(price), sum(sold_price) from properties where sold_price is not null group by agent
--select distinct (sum(price) - sum(bedrooms)) / count(bedrooms) from properties where location = 'Angel'
--select * from properties where location = 'Angel' and sold_price is not null

--select * from properties * where location = 'Angel'
--select distinct location, sum(price) / sum(bedrooms), sum(price), sum(bedrooms) from properties where location = 'Angel' group by location
--select avg(price / bedrooms) from properties where location = 'Angel'
--select * from properties  where location = 'Angel' and bedrooms = 2 and property_type = 'house' and date_sold is not null
--update properties set date_sold  = date_sold + interval'1 month' where location = 'Angel' and bedrooms = 2 and property_type = 'house' and date_sold is not null

-- day two of sql

--drop table product_order
--drop table shop_order
--drop table customer
--drop table review
--drop table product

--create table product (
--	id serial,
--	name varchar(50) not null,
--	price int not null,
--	primary key (id)
--);
--
--create table review (
--	id serial,
--	product_id int,
--	rating int,
--	review text,
--	foreign key (product_id) references product (id),
--	primary key (id)
--);
--
--create table customer (
--	id serial,
--	username varchar(50) not null,
--	primary key (id)
--);
--
--create table shop_order (
--	id serial,
--	customer_id int,
--	foreign key (customer_id) references customer (id),
--	primary key (id)
--);
--
--create table product_order (
--	id serial,
--	product_id int not null,
--	quantity int,
--	shop_order_id int,
--	foreign key (shop_order_id) references shop_order (id),
--	foreign key (product_id) references product (id),
--	primary key (id)
--);
--
--
--insert into product (name, price) values ('world of warcraft', 40 );
--insert into product (name, price) values ('pubg', 20 );
--insert into product (name, price) values ('rocket league', 35 );
--insert into product (name, price) values ('divinity', 80 );
--insert into product (name, price) values ('witcher', 1 );
--insert into product (name, price) values ('assassins creed', 26 );
--insert into product (name, price) values ('half life 3', 10 );
--insert into product (name, price) values ('football', 0 );
--
--
--insert into customer (username) values ('hamz91');
--insert into customer (username) values ('Leafeh');
--insert into customer (username) values ('pimp');
--
--
--insert into review (product_id, rating, review) values (1, 5, 'fecking awesome');
--insert into review (product_id, rating, review) values (2, 5, 'really good');
--insert into review (product_id, rating, review) values (3, 3, 'good but tedious');
--insert into review (product_id, rating, review) values (4, 3, 'fun for a bit');
--insert into review (product_id, rating, review) values (5, 5, 'sweet af');
--insert into review (product_id, rating, review) values (6, 5, 'geralt is a bossman');
--insert into review (product_id, rating, review) values (7, 4, 'altair over ezio');
--insert into review (product_id, rating, review) values (8, 5, 'so good it doesnt exist');
--insert into review (product_id, rating, review) values (8, 1, 'so good it doesnt exist');
--insert into review (product_id, rating, review) values (8, 1, 'so good it doesnt exist');
--insert into review (product_id, rating, review) values (2, 1, 'so good it doesnt exist');
--insert into review (product_id, rating, review) values (5, 1, 'so good it doesnt exist');
--
--
--insert into shop_order (customer_id) values (1);
--insert into shop_order (customer_id) values (2);
--insert into shop_order (customer_id) values (3);
--
--
--insert into product_order (product_id, quantity, shop_order_id) values (8, 1, 2);
--insert into product_order (product_id, quantity, shop_order_id) values (3, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (7, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (1, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (2, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (6, 1, 2);
--insert into product_order (product_id, quantity, shop_order_id) values (5, 1, 2);
--insert into product_order (product_id, quantity, shop_order_id) values (3, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (1, 1, 3);
--insert into product_order (product_id, quantity, shop_order_id) values (2, 1, 2);
--insert into product_order (product_id, quantity, shop_order_id) values (3, 1, 1);
--insert into product_order (product_id, quantity, shop_order_id) values (5, 1, 3);

--select product.id, product.name, avg(review.rating) 
--from product, review
--where product.id = review.product_id
--group by "name", product.id

--select product.id, product.name, sum(product_order.quantity), product_order.product_id
--from product, product_order
--where product.id = product_order.product_id
--group by product.id, product_order.product_id

--alter table product_order alter quantity type int
--alter table product alter price type int

--select sum(total_price)from (select product.id, product.name, product_order.product_id, (product.price * product_order.quantity) as total_price from product, product_order
--where product.id = product_order.product_id) as quantative_price

--select product.id, product.name, (product.price * sum(product_order.quantity)) as total_price, sum(product_order.quantity) as quatity
--from product, product_order
--where product.id = product_order.product_id
--group by product.id, product.name, product_order.quantity

--select product.id, product.name, customer.id, shop_order.customer_id, (product.price * sum(product_order.quantity)) as total_price, sum(product_order.quantity) as quatity
--from product, product_order, customer, shop_order
--where product.id = product_order.product_id and customer.id = shop_order.customer_id 
--group by product.id, product.name, customer. id, shop_order.customer_id, product_order.quantity

--select * from customer

--select customer.username,
--(product.price * sum(product_order.quantity)) as total_price,
--sum(product_order.quantity) as quatity
--from product, product_order, customer, shop_order
--where customer.id = product_order.shop_order_id
--group by product.id, product.name, customer.username, product_order.quantity



--select username, sum(product_sum) from (select customer.username, shop_order.id, product.name, (product.price*product_order.quantity) as product_sum from product, product_order,customer, shop_order 
--where customer.id = shop_order.customer_id 
--and product.id = product_order.product_id 
--and product_order.shop_order_id = shop_order.id) as sums group by username order by username

--select customer.username, sum(product.price*product_order.quantity) as total
--from product, product_order,customer, shop_order 
--where customer.id = shop_order.customer_id 
--and product.id = product_order.product_id 
--and product_order.shop_order_id = shop_order.id
--group by username
--order by username


select customer.username, product.name, sum(product_order.quantity)
from customer, product_order, product, shop_order
where customer.id = shop_order.customer_id
and shop_order.id = product_order.shop_order_id
and product_order.product_id = product.id
group by customer.username, product.name, product_order.quantity 


--CREATE DATABASE dotify;


--DROP TABLE song_playlist;
--DROP TABLE song;
--DROP TABLE artist;
--DROP TABLE playlist;
--
--
--
--
--CREATE TABLE artist (
--    id serial,
--    name varchar(50) NOT NULL,
--    email varchar(50) NOT NULL UNIQUE,
--    PRIMARY KEY (id)
--);
--CREATE TABLE song (
--    id SERIAL,
--    artist_id INT,
--    title VARCHAR(50) NOT NULL,
--    year SMALLINT NOT NULL,
--    PRIMARY KEY (id),
--    FOREIGN KEY (artist_id) REFERENCES artist (id)
--);
--INSERT INTO artist VALUES (1, 'The Doors', '[email protected]');
--INSERT INTO artist VALUES (2, 'The Kinks', '[email protected]');
--ALTER SEQUENCE artist_id_seq RESTART WITH 3 INCREMENT BY 1;
--
--INSERT INTO song VALUES (1, 1, 'Riders On The Storm', 1971);
--INSERT INTO song VALUES (2, 1, 'Light My Fire', 1967);
--INSERT INTO song VALUES (3, 1, 'Break On Through', 1967);
--INSERT INTO song VALUES (4, 2, 'Lola', 1970);
--INSERT INTO song VALUES (5, 2, 'Waterloo Sunset', 1967);
--INSERT INTO song VALUES (6, 2, 'Sunny Afternoon', 1966);
--ALTER SEQUENCE song_id_seq RESTART WITH 7 INCREMENT BY 1;
--
--CREATE TABLE playlist (
--    id serial,
--    name varchar(50) NOT NULL,
--    PRIMARY KEY (id)
--);
--
--
--CREATE TABLE song_playlist (
--    id serial,
--    song_id INT NOT NULL,
--    playlist_id INT NOT NULL,
--    PRIMARY KEY (id),
--    FOREIGN KEY (song_id) REFERENCES song (id),
--    FOREIGN KEY (playlist_id) REFERENCES playlist (id)
--);
--
--
--INSERT INTO playlist VALUES (1, 'Random favourites');
--INSERT INTO playlist VALUES (2, 'Other songs');
--ALTER SEQUENCE playlist_id_seq RESTART WITH 3 INCREMENT BY 1;
--
--INSERT INTO song_playlist VALUES (1, 1, 1);
--INSERT INTO song_playlist VALUES (2, 2, 1);
--INSERT INTO song_playlist VALUES (3, 3, 1);
--INSERT INTO song_playlist VALUES (4, 4, 1);
--INSERT INTO song_playlist VALUES (5, 2, 2);
--INSERT INTO song_playlist VALUES (6, 3, 2);
--INSERT INTO song_playlist VALUES (7, 5, 2);
--ALTER SEQUENCE song_playlist_id_seq RESTART WITH 8 INCREMENT BY 1;

--select song.id, artist.name, song.title
--from song, artist
--where song.artist_id = artist.id
--and song.id = 5

--SELECT playlist.id, playlist.name, song.id as song_id, artist.name as artist_name, song.title
--FROM playlist, song_playlist, song, artist
--WHERE playlist.id = song_playlist.playlist_id
--and song_playlist.song_id = song.id
--and song.artist_id = artist.id
--AND playlist.id = 2

--SELECT playlist.id, playlist.name
--FROM playlist
--WHERE playlist.id = 2

--SELECT song.id as song_id, artist.name as artist_name, song.title
--FROM song_playlist, song, artist
--WHERE song_playlist.playlist_id = 1
--and song_playlist.song_id = song.id
--and song.artist_id = artist.id
--

--update artist 
--set name = $1, email = $2
--where id = $3
--delete playlist
--WHERE 1 = playlist.id
--delete song_playlist
--WHERE 1 = song_playlist.playlist_id
--
--delete song
--WHERE 1 = song.id
--
--delete artist
--WHERE 1 = artist.id

--select song.id
--from song, artist
--where artist.id = 1

--        select song_playlist.song_id
--        FROM song_playlist
--        WHERE song_playlist.song_id = 1

--    select * FROM song_playlist, song 
--    WHERe song.id = song_playlist.song_id
--    AND song.artist_id = 1;
   
--   select * from song
--   where song.artist_id = 1

--   select * from artist
--   where artist.id = 1


--    DELETE FROM song_playlist using song 
--    WHERE song.id = song_playlist.song_id
--    AND song.artist_id = 1;
--      DELETE from song
--   where song.artist_id = 1
--      DELETE from artist
--   where artist.id = 1