#35 - Sql

#35 - Sql

By Ifeanyi Omeata


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 :) ): image.png

image.png


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);

image.png

image.png


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

image.png


14. SQL - Select Columns from Table


>>Return to Menu

SELECT CustomerName, City FROM Customers;
SELECT firstname, lastname FROM people;

image.png


15. SQL - Select by value from Table


>>Return to Menu

SELECT * FROM people
WHERE lastname = 'Roberts';

image.png


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


>>Return to Menu

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

image.png


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


>>Return to Menu

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

image.png


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;

image.png


20. SQL - Order By Descending from Table


>>Return to Menu

SELECT * FROM Customers
ORDER BY Country DESC;
SELECT * FROM people
ORDER BY age DESC;

image.png


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


>>Return to Menu

SELECT * FROM people
ORDER BY firstname;

image.png

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

image.png


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;

image.png

image.png


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;

image.png

image.png

#End


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


Get in Touch:
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