sql

-- -- create
-- CREATE TABLE EMPLOYEE (
-- empId INTEGER PRIMARY KEY,
-- name TEXT NOT NULL,
-- dept TEXT NOT NULL
-- );

-- -- insert
-- INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
-- INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
-- INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

-- -- fetch
-- SELECT * FROM EMPLOYEE WHERE dept = 'Sales';

-- CREATE TABLE EMPLOYEE (
-- id INT PRIMARY KEY AUTO_INCREMENT,
-- name TEXT NOT NULL,
-- department TEXT NOT NULL
-- );

-- INSERT INTO EMPLOaeYEE VALUES ('John Doe', 'Sales');
-- INSERT INTO EMPLOYEE VALUES ('John Doe', 'Sales');

-- SELECT * from EMPLOYEE;

-- CREATE TABLE test(
-- id INT NOT NULL ,
-- test_Coloumn INT,

-- );

-- ALTER TABLE test ADD ad VARCHAR(23);

-- INSERT INTO TABLE test(test_Coloumn,ad) VALUES(,1212,'dwdwd');

-- SELECT * FROM test;

-- -- drop TABLE test;

CREATE TABLE band(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE albums(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
release_year INT,
band_id INT NOT NULL,
PRIMARY KEY (id),
foreign KEY (band_id) references band(id)
);

INSERT INTO band(name) VALUES ('chain');

INSERT into band(name) VALUES('smokers'),('one'),('direction');

SELECT * from band;
-- limikting and orderd
SELECT * from band order by name desc limit 2;

-- rename
SELECT name as 'Band Name' from Band;

SELECT max(name) from band order by name desc;

INSERT INTO albums(name,release_year,band_id)
VALUES ('the nigthmare',2017,1),
('all we know',2007,1) ,
('night',2018,2),
('alone',2011,3),
('hope',NULL,4);

SELECT * from albums;

SELECT * from albums
where band_id!=2
and id not in (SELECT id from albums where band_id=2);

SELECT * from albums order by name desc;

update albums
set release_year=2015
where id=1;

SELECT * from albums;
SELECT * from albums where release_year > 2000 and release_year <2016;

SELECT * from albums where name LIKE "%ni%" or band_id=3;

SELECT * from albums;

SELECT * from albums
where release_year is null;

delete from albums
where id=5;
SELECT * from albums;

SELECT * from band
join albums on band.id=albums.band_id;

SELECT * from albums
inner join band on band.id=albums.band_id;

SELECT * from band
left join albums on band.id=albums.band_id;

SELECT sum(release_year) from albums;

SELECT band_id from albums;

SELECT band_id,count(id) from albums
group by band_id;

SELECT b.name as band_name , count(a.id) as num_albums
from band as b
left join albums as a
on b.id=a.band_id

group by b.id;

SELECT b.name as band_name , count(a.id) as num_albums
from band as b
left join albums as a
on b.id=a.band_id
where b.id>1
group by b.id;

SELECT b.name as band_name , count(a.id) as num_albums
from band as b
left join albums as a
on b.id=a.band_id
group by b.id
having num_albums =0
;