SQL

Topics:

1. SQL - Create Database
2. SQL - Drop Database
3. SQL - Backup Database
4. SQL - Create Table
5. SQL - Drop Table
6. SQL - Truncate Table
7. SQL - Alter Table-Add Column
8. SQL - Alter Table-Drop Column
9. SQL - Alter Table-Alter Column
10. SQL - Check
11. SQL - Default
12. SQL - Insert Into Table
13. SQL - Select all from Table
14. SQL - Select Columns from Table
15. SQL - Select by value from Table
16. SQL - Select by conditional 'OR' from Table
17. SQL - Select by conditional 'AND' from Table
18. SQL - Select distinct from Table
19. SQL - Order By Ascending from Table
20. SQL - Order By Descending from Table
21. SQL - Order By Column(s) from Table
22. SQL - Update Records in Table
23. SQL - Delete Records in Table

1. SQL - Create Database

>>Return to Menu

CREATE DATABASE mydata;

2. SQL - Drop Database

>>Return to Menu

DROP DATABASE mydata;
DROP DATABASE IF EXISTS mydata;

3. SQL - Backup Database

>>Return to Menu

BACKUP DATABASE mydata
TO DISK = 'D:\backups\mydata.bak'
WITH DIFFERENTIAL;

4. SQL - Create Table

>>Return to Menu

CREATE TABLE users (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    username varchar(255) UNIQUE NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    created_at date NOT NULL,
    updated_at date,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS songs (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    user_id  int NOT NULL,
    name varchar(255) NOT NULL,
    genre varchar(255) NOT NULL,
    created_at date NOT NULL,
    updated_at date,
    PRIMARY KEY (id)
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Note: I am using pgAdmin4 not psequel ( I don't fancy psequel :) ):

5. SQL - Drop Table

>>Return to Menu
The DROP TABLE statement is used to drop an existing table in a database.

DROP TABLE users;

6. SQL - Truncate Table

>>Return to Menu
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE users;

7. SQL - Alter Table-Add Column

>>Return to Menu
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE users
ADD age int;

8. SQL - Alter Table-Drop Column

>>Return to Menu

ALTER TABLE users
DROP COLUMN age;

9. SQL - Alter Table-Alter Column

>>Return to Menu

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

10. SQL - Check

>>Return to Menu

CREATE TABLE Persons (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    username varchar(255) UNIQUE NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    age int,
    CHECK (age>=18)
);

11. SQL - Default

>>Return to Menu

CREATE TABLE Persons (
    id int UNIQUE NOT NULL AUTO_INCREMENT,
    username varchar(255) UNIQUE NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    age int,
    city varchar(255) DEFAULT 'toronto'
);

ALTER TABLE Persons
ALTER city SET DEFAULT 'toronto';

12. SQL - Insert Into Table

>>Return to Menu

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

INSERT INTO people (id,firstname,lastname) 
VALUES (1,'Ifeanyi','Omeata');

INSERT INTO public.people (id, firstname, lastname) 
VALUES ('2'::integer, 'Ifeanyi'::character varying, 'Omeata'::character varying);

13. SQL - Select all from Table

>>Return to Menu

SELECT column1, column2, ...
FROM table_name;

SELECT * FROM users;

SELECT * FROM people
ORDER BY id ASC

14. SQL - Select Columns from Table

>>Return to Menu

SELECT CustomerName, City FROM Customers;

SELECT firstname, lastname FROM people;

15. SQL - Select by value from Table

>>Return to Menu

SELECT * FROM people
WHERE lastname = 'Roberts';

16. SQL - Select by conditional 'OR' from Table

>>Return to Menu

SELECT * FROM people
WHERE lastname = 'Roberts'
OR age < 40;

17. SQL - Select by conditional 'AND' from Table

>>Return to Menu

SELECT * FROM people
WHERE lastname = 'Roberts'
OR age < 40
AND id < 3;

18. SQL - Select distinct from Table

>>Return to Menu
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT DISTINCT Country FROM Customers;

SELECT COUNT(DISTINCT Country) FROM Customers;

19. SQL - Order By Ascending from Table

>>Return to Menu

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM people
ORDER BY age;

SELECT * FROM people
ORDER BY age ASC;

20. SQL - Order By Descending from Table

>>Return to Menu

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM people
ORDER BY age DESC;

21. SQL - Order By Column(s) from Table

>>Return to Menu

SELECT * FROM people
ORDER BY firstname;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

SELECT * FROM people
ORDER BY firstname, lastname;

22. SQL - Update Records in Table

>>Return to Menu
The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

UPDATE people 
SET age = 40
WHERE id = 1;

23. SQL - Delete Records in Table

>>Return to Menu
The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

DELETE FROM Customers;

DELETE FROM people 
WHERE id = 1;

#End

Hope you enjoyed this! :) Follow me for more contents...

Get in Touch:
ifeanyiomeata.com
contact@ifeanyiomeata.com

Youtube: youtube.com/c/IfeanyiOmeata
Linkedin: linkedin.com/in/omeatai
Twitter: twitter.com/iomeata
Github: github.com/omeatai
Stackoverflow: stackoverflow.com/users/2689166/omeatai
Hashnode: hashnode.com/@omeatai
Medium: medium.com/@omeatai
© 2022

#35 - Sql

By Ifeanyi Omeata