Basics of structured query language

sql-basics.sql

SQL BASICS


CREATE DATABASE local;

-- tell mysql which database to work with
USE local;

-- create a table with a column
create table test (
 	title TEXT
);

-- add another column to the table
ALTER TABLE test
ADD another_column varchar(255);

-- remove table
DROP TABLE test;


DROP DATABASE local;

A Record Company example

A Record Company example

CREATE DATABASE record_company;

USE record_company;

-- Primary Key
CREATE TABLE bands (
	id INT NOT NULL auto_increment,
	name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- FOREIGN KEY
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 bands(id)
);

-- Insert a single band name
INSERT INTO bands(name)
VALUES ('Coldplay');

-- Insert multiple band names
INSERT INTO bands(name)
VALUES ('Pearl Jam'), ('REM'), ('Girl in Red');

-- Show all bands
SELECT * FROM BANDS;

-- first two bands
SELECT * FROM BANDS LIMIT 2;

-- get specific column
SELECT name FROM BANDS;

-- Change the name of the column (aliasing)
SELECT id AS `ID`, name AS 'Band Name'
FROM BANDS;

-- order by name
SELECT * FROM bands ORDER BY name ASC; DEFAULT ASC
SELECT * FROM bands ORDER BY name DESC;

-- Add and album of a band
INSERT INTO albums (name, release_year, band_id)
VALUES ('Y and X', 1992, 1);

-- Add multiple albums
INSERT INTO albums (name, release_year, band_id)
VALUES ('Test Album', 1993, 1),
		('Test Album', 1994, 2),
	        ('Test Album', 1995, 3);

-- Return unique items
select DISTINCT name from albums;

-- Update the release year for Coldplay's album with id 1
UPDATE albums
SET release_year = 1982
WHERE id = 1;

-- filter data
SELECT * FROM albums
WHERE release_year < 1995;


-- filter data
SELECT * FROM albums
WHERE release_year < 1995;

-- Like STRING filter with OR
SELECT * FROM albums
where name LIKE '%and%' OR band_id = 2;

-- Like STRING filter with AND
SELECT * FROM albums
where release_year = 1982 AND band_id = 1;

-- BETWEEN MIN AND MAX Values
SELECT * FROM albums
where release_year BETWEEN 1992 AND 1994;

-- WHERE value IS NULL check
SELECT * FROM albums
where release_year IS NULL;

-- Remove the row with NULL release_year
DELETE FROM albums where id = 5;