#35 - Sql
By Ifeanyi Omeata
Updated
•6 min read
I
Hi, I am a Software Developer of 3-4 years specialising in React, Javascript, Node, NextJS, Express, Python, Django, Fast API, SQL and a few other technology stack, with a good background in Networking and Cloud Infrastructure. I am working to become a DevOps Solutions Engineer and happily married to my long time girlfriend.
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:
www.ifeanyiomeata.com
contact@ifeanyiomeata.com
Youtube: https://www.youtube.com/c/IfeanyiOmeata
Linkedin: https://www.linkedin.com/in/omeatai/
Twitter: https://twitter.com/iomeata
Github: https://github.com/omeatai/
Stackoverflow: https://stackoverflow.com/users/2689166/omeatai
Hashnode: https://hashnode.com/@omeatai
Medium: https://medium.com/@omeatai
© 2022
