SQL
Structured Query Language is used to access and manipulate databases.
Relational Database Management System is the basis for SQL. The data in RDBMS is stored in Tables. A Table consists of rows (Tuples) and Columns (Attributes).
SQL keywords are not case sensitive.
1. Data Definition Language (DDL):
DDL (Data Definition Language) is a subset of SQL that is used to define and manage the structure of a database. It includes commands such as creating databases, tables, and modifying their structure.
- CREATE DATABASE: Creates a new database.
CREATE DATABASE databasename;
- DROP DATABASE: Delete an existing database.
DROP DATABASE databasename;
- CREATE TABLE: Creates a new table.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
- DROP TABLE: Delete an existing table.
DROP TABLE tablename;
- TRUNCATE TABLE: Delete the data inside the table and not the table itself.
TRUNCATE TABLE tablename;
- ALTER TABLE: Modifies an existing table’s structure.
ALTER TABLE tablename
ADD COLUMN columnname datatype;
ALTER TABLE tablename
DROP COLUMN columnname;
ALTER TABLE tablename
RENAME COLUMN oldname TO newname;
ALTER TABLE tablename
MODIFY COLUMN columnname datatype;
- CONSTRAINTS: Rules applied to the data columns.
On Creation:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
....
);
After Creation:
ALTER TABLE table_name
ADD CONSTRAINT constr_name constraint (col1,col2,...);
i. NOT NULL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
--OR
ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;
ii. UNIQUE:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
--OR
ALTER TABLE Persons
ADD UNIQUE (ID);
iii. PRIMARY KEY:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
--OR
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
iv. FOREIGN KEY:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
--OR
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
v. CHECK:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
--OR
ALTER TABLE Persons
ADD CHECK (Age>=18);
vi. DEFAULT:
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
--OR
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
- CREATE INDEX: Creates an index on a table.
CREATE INDEX idx_lastname
ON Employees(LastName);
- AUTOINCREMENT: Allows a unique number to be generated automatically at a field.
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
- CREATE VIEW: Creates a virtual table.
-- Creation of virtual table
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
-- Using the virtual table
SELECT * FROM view_name;
2. Data Manipulation Language (DML):
Used for manipulating the data within the database. It includes commands such as SELECT, INSERT, UPDATE, and DELETE.
- SELECT: Retrieves data from one or more tables.
SELECT * FROM Employees
WHERE DepartmentID = 100;
- INSERT INTO: Adds new records to a table.
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (1, 'John', 'Doe', 100); - UPDATE: Modifies existing records in a table.
UPDATE Employees SET DepartmentID = 200 WHERE EmployeeID = 1; - DELETE: Removes records from a table.
DELETE FROM Employees WHERE EmployeeID = 1; - JOIN (INNER JOIN): Combines rows from two or more tables based on a related column. ```sql SELECT Orders.OrderID, Customers.CustomerName FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
– This is equivalent to the ‘comma’ syntax SELECT Orders.OrderID, Customers.CustomerName FROM Orders, Customers WHERE Orders.CustomerID = Customers.CustomerID;
6. **LEFT JOIN**: Returns all records from left table, and matching records from right table (NULL if no match on the right table)
7. **RIGHT JOIN**: Returns all records from right table, and matching records from left table (NULL if no match on the left table)
8. **UNION**: Combine the result set of two or more `SELECT` statements.
```sql
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
where
The WHERE clause in SQL is used to filter records. It’s used to extract only those records that fulfill a specified condition.
SELECT * FROM Employees WHERE Salary > 50000;
The WHERE clause can be combined with logical operators like AND, OR, and NOT, as well as comparison operators like =, <>, <, >, <=, >=.
LIKE is a simple pattern matching operator that allows us to use the wildcards:
%- Matches any sequence of characters_- Matches any single character
SELECT * FROM employees WHERE last_name LIKE 'S%';
IN is used to mention all possible values as an array.
SELECT * FROM employees WHERE last_name IN ('Ron', 'Harry');
BETWEEN is used to provide a range of values as an array, which satisfies the condition.
SELECT * FROM employees WHERE YEAR(hire_date) BETWEEN 1999 AND 2001;
3. DCL (Data Control Language):
Used for controlling the access to data within the database. It includes commands such as GRANT and REVOKE.
4. TCL (Transaction Control Language):
Used for managing transactions within a database. It includes commands such as COMMIT, ROLLBACK, and SAVEPOINT.
PL/SQL
PL/SQL (Procedural Language/Structured Query Language) is a programming language used for developing applications within Oracle databases. It extends SQL (Structured Query Language) by adding procedural capabilities, allowing developers to write procedural code such as loops, conditional statements, and error handling directly within SQL blocks. PL/SQL is commonly used to create stored procedures, functions, triggers, and packages, which enhance the functionality and performance of Oracle database applications.
1. Declare a Variable
DECLARE my_variable VARCHAR(50);
2. Create a Simple Loop
DECLARE counter INT DEFAULT 1;
WHILE counter <= 5 DO
SELECT counter;
SET counter = counter + 1;
END WHILE;
3. Create a Conditional Statement
IF x > 5 THEN
SELECT 'x is greater than 5';
ELSE
SELECT 'x is not greater than 5';
END IF;
4. Create a Function
CREATE FUNCTION calculate_area(length FLOAT, width FLOAT)
RETURNS FLOAT
BEGIN
DECLARE area FLOAT;
SET area = length * width;
RETURN area;
END;
5. Create a Procedure
Procedures (PL/SQL Blocks) have no return values. These are used for operations to be performed multiple times.
CREATE PROCEDURE display_message(IN message VARCHAR(255))
BEGIN
SELECT message;
END;
The IN indicates that a parameter is an INPUT parameter. We provide a value for this parameter when the procedure is called. Also IN is the default mode of a parameter, and hence can be avoided.
Other keywords are OUT (representing an output parameter) and INOUT (a parameter that can be both read and modified).
MySQL
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for adding, accessing, and managing content in a database. It’s known for its speed, reliability, and flexibility.
MySQL provides a rich set of built-in functions that you can use in SQL statements.
- String Functions: Manipulate and process string data.
CONCAT(str1, str2, ...): Returns a string that results from concatenating the arguments.LENGTH(str): Returns the length of a string in bytes.SUBSTRING(str, pos, len): Returns a substring from a string starting at a specified position.
- Numeric Functions: Perform operations on numeric data.
ABS(X): Returns the absolute value of X.CEIL(X) or CEILING(X): Returns the smallest integer value not less than X.FLOOR(X): Returns the largest integer value not greater than X.
- Date and Time Functions: Process date and time data.
NOW(): Returns the current date and time.CURDATE(): Returns the current date.DATEDIFF(expr1, expr2): Returns the difference in days between two date expressions.
- Control Flow Functions: Implement conditional logic.
IF(expr1, expr2, expr3): If expr1 is TRUE, IF() returns expr2; otherwise, it returns expr3.CASE: Implements a complex conditional construct.
- Aggregate Functions: Perform a calculation on a set of values and return a single value.
AVG(expr): Returns the average value of expr.SUM(expr): Returns the sum of expr.COUNT(expr): Returns a count of the number of non-NULL values of expr.
- Comparison Functions: Compare values and return a logical value.
COALESCE(value,...): Returns the first non-NULL value in the list.GREATEST(value1,value2,...): Returns the greatest value in the list of arguments.LEAST(value1,value2,...): Returns the smallest value in the list of arguments.
- Conversion Functions: Convert a value from one data type to another.
CAST(expr AS type): Converts a value to a specified type.CONVERT(expr,type): Converts a value to a specified type.
- Information Functions: Provide information about databases, tables, and server settings.
DATABASE(): Returns the default (current) database name.USER(): Returns the current MySQL user name and host name.