Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

SQL BASICS

SQL may be said to consist of 4 languages

Data Query Language which is used to query the database for information

Data Definition Language which is used for defining schemas

Data Control Language which is used for controlling access to the data in the database. It’s also used for user permissions management

Data Manipulation Language for inserting, updating and deleting data from the database

A query is a set of instructions written in SQL that tells the RDBMS to retrieve certain information for you

Visit mysql community server to download MySql RDBMS

Data Types

INT (whole numbers)

DECIMAL (M, N) (M stands for displace length and N for number of decimals)

VARCHAR(l) (variable character used to store a string of text with length l)

BLOB (Binary Large Object, stores large data)

DATE (yyyy-mm-dd)

TIMESTAMP (yyyy-mm-dd hh:mm:ss)

Operators

Comparison operators

< less than

> Greater than

<= less than or equal to >= greater than or equal to = equal to

<> Not equal to AND OR

WHERE student_id <= 3 AND name <> ‘Jack’

WHERE name IN (‘Claire’, ‘Kate’, ‘Mike’) means the name is Claire, Kate or MikeSELECT *

from student;

DELETE FROM student

WHERE student_id = 5

CREATE query

CREATE TABLE student (

    student_id INT PRIMARY KEY,

    name VARCHAR(20) NOT NULL,

    major VARCHAR(10) UNIQUE / major VARCHAR(10) DEFAULT = ‘undecided’

);

A primary key is both null and unique

SQL Constraints

These are the rules applied to a data column or the whole table to limit the type of data that can go into it. The RDBMS checks when the user performs an INSERT/UPDATE/DELETE whether it violates an existing constraints.

They include NOT NULL, UNIQUE, DEFAULT *Value*, PRIMARY KEY, FOREIGN KEY, CHECK (*condition*)

CREATE TABLE table_name (

  column1 datatype constraint);

INSERT query

INSERT INTO table_name (column1, column2)

VALUES (value1, value2)

INSERT INTO student (student_id, name) VALUES (3, ‘Claire’);

Ensure the values passed match the datatype and number of columns.

You can populate the data into a table from an already existing table

INSERT INTO first_table [(column_names)]

SELECT column1, column2

FROM second_table

[WHERE condition];

If you have 2 ables with the same structure, you can insert all the contents of one into the other

INSERT INTO table_name TABLE second_table

SELECT query

The SELECT statement is used to fetch data from a database table

SELECT *

FROM table_name

The asterisk fetches all fields

THE SELECT statement can also perform mathematical operations

SELECT 25*48 returns 1200

You can display the column name as another name

SELECT column_name as alias_name

FROM table_name

SELECT CONCAT(first_name, “ “, last_name) AS full_name, address
FROM Customers ORDER BY last_name;

The SELECT INTO statement creates a new table and inserts data from an existing table

SELECT * INTO new_table from existing_table

You can copy specific records into a new table

SELECT * INTO new_table FROM existing_table WHERE condition;

You can copy data from multiple tables using the JOIN clause

SELECT column1, column2 INTO new_table FROM table1

JOIN table2 ON table1.column = table2.column

We can select top rows in the SELECT query

SELECT TOP 5 * FROM customers

SELECT TOP 40 PERCENT * FROM customers ORDER BY salary

You can list all tables available in a selected database

USE testDB;

SHOW TABLES;

Use this to rename a table

RENAME TABLE table_name TO new_tableName;

You can use the ALTER statement to change or modify the structure of an existing table

ALTER TABLE table_name RENAME [TO|AS] new_tableName

To add a column to a table,
ALTER TABLE Customers ADD SEX char(1);

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

PRMARY KEY (column1, column2…);

To modify datatype, ALTER TABLE table_name ALTER COLUMN column_name datatype;

You can delete, drop, truncate, clone tables

TRNCATE TABLE Customers;

CREATE TABLE newTable SELECT * FROM originalTable;

CREATE TABLE NewCustomers SELECT * FROM Customers;

DROP TABLE [IF EXISTS] table_name;

DELETE FROM table_name [WHERE condition]; will delete the data but not any constraints, triggers or indexes in the table

DELETE FROM Customers WHERE NAME = ‘Ajayi’; will delete all the affected rows

This is used to update existing records in a table without affecting the structure. It is used along with SET keyword

SELECT *

from student;

UPDATE student

SET major = ‘Bio’, age = 18

WHERE major = ‘Biology’;

This updates the student table where major = biology

WHERE student_id = 4;

SET major = ‘BioChemistry’

WHERE major = ‘Biology’ OR major = ‘Chemistry’;

ORDER BY

This is used to sort data in ascending or descending order

SELECT column1, column2 FROM table_name

WHERE condition

ORDER BY column1 ASC

WHERE clause

The WHERE clause is used to filter results

SELECT column1, column2 FROM table_name WHERE condition;

SELECT ID, name, salary FROM customers WHERE salary > 2000;

You can use the IN clause to specify values that match values from a specified list

SELECT * from customers

WHERE name IN (‘Dipo’, ‘Tunde’, ‘John’);

Using WHERE with LIKE operator, we can filter rows that match a particular pattern

SELECT * FROM customers WHERE name like ‘k__%’;

We can use logical operators AND/OR with the WHERE clause

SELECT * FROM customers

WHERE (age = 25 OR salary < 4500) AND (name = ‘Dipo’ OR name = ‘Tunde’);

DISTINCT keyword

SELECT DISTINCT column1, column2

FROM table_name

SELECT COUNT(DISTINCT column_name)

FROM table_name WHERE condition;

GROUPBY keyword

This is used to arrange identical data into groups. It is used with the SELECT statement and typically used with aggregate functions such as SUM(), COUNT(), AVG (), MAX(), MIN()

SELECT column_name

FROM table_name

GROUP BY column

SELECT AGE, COUNT(Name)  FROM customers GROUP BY age;

HAVING keyword

The having is used like the WHERE clause in the function of filtering rows in a table based on a condition. The HAVING clause is usually followed by the GROUP BY clause abd can be used with aggregate functions such as COUNT(), SUM(), AVG()

SELECT column1, column2 FROM table_name GROUP BY column1

HAVING condition;

SELECT address, age, min(salary) as min_sum FROM customers GROUP BY address, age HAVING age > 25;

BETWEEN operator

The BETWEEN operator is a logical operator in SQL used to retrieve the data within a specified range. It’s like a combination of greater than or equal AND less than or equal to

SELECT colmn1, column2 FROM table_name WHERE column_name BETWEEN value1 AND value2 [or BETWEEN ‘A’ and ‘L’]

UNION operator

This is used to combine data from multiple tables and eliminating duplicate rows if it finds any. NOTE that to use union, the number of columns must be in the same order and datatype.

SELECT column1, column2 FROM table_name [WHERE condition]

UNION

SELECT columnA, columnB FROM table_name2 [WHERE condition]

INTERSECT and EXCEPT operator

INTERSECT operator: This combines two SELECT statements but returns only rows from the first SELECT statement identical to a row in second SELECT statement. It works like the intersect operator in set theory.

SELECT column1, column2 FROM table_name

INTERSECT

SELECT columnA FROM table_name2

EXCEPT operators combines two SELECT statement and returns rows from the first not returned in the second SELECT statement

SELECT column1, column2 FROM table1 EXCEPT

SELECT columnA, column FROM table_name2

JOINS

We use the JOIN clause is used to combine data from two or more tables in a database

Inner Join: this is the default join which retrieves the intersection of two tables.

Outer Join: this retrieves all the records in two tables even if there are no common rows

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.