CREATE DATABASE test;
DROP DATABASE test;
CREATE TABLE test (
test_table INT
);
ALTER TABLE test
ADD another_column VARCHAR(255);
----------------x-----------------
CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands (
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 bands (name)
VALUES ('Iron Maiden');
INSERT INTO bands (name)
VALUES ('Deuce'), ('Avenged Sevenfold'), ('Ankor');
SELECT * FROM bands; // will full table
SELECT * FROM bands LIMIT 2; // will give first 2 entries of table
SELECT name FROM bands;
SELECT id AS 'ID', name AS 'Band Name' FROM bands;
SELECT * FROM bands ORDER BY name DESC;
INSERT INTO alumbs (name,release_year, band_id)
VALUES ('The Number of the Beasts', 1985, 1),
('Power Slave', 2018, 2),
('Nightmare', 2018, 2),
('Nightmare', 2010, 3),
('Test Album', NULL, 3);
SELECT * FROM albums;
SELECT DISTINCT name FROM albums;
UPDATE albums
SET release_year = 1982
WHERE id = 1;
SELECT * FROM albums
WHERE release_year < 2000;
SELECT * FROM albums
WHERE name LIKE '%er%';
SELECT * FROM albums
WHERE release_year = 1984 AND(/OR) band_id = 1;
SELECT * FROM albums
WHERE release_year BETWEEN 2000 AND 2018;
SELECT * FROM albums
WHERE release_year IS NULL;
DELETE FROM albums; / will delete data in table
DELETE FROM albums WHERE id = 5;
// JoINING TABLES
//INNER JOIN --> returns only those values whose match it is able to find.
(Matlab ki left and right dono tables me value honi chaiye unke conidition wale
column me)
SELECT * FROM bands
INNER JOIN albums ON bands.id = albums.band_id;
//Everything on left table will be on master table
SELECT * FROM bands
LEFT JOIN albums ON bands.id = albums.band_id;
//Everything on right table will on master table
SELECT * FROM bands
RIGHT JOIN albums ON bands.id = albums.band_id;
//Aggregate Functions
SELECT AVG(release_year) FROM albums;
SELECT SUM(release_year) FROM albums;
SELECT band_id, COUNT(band_id) FROM albums
GROUP BY band_id;
