Basic SQL

Basic SQL Queries

SQL vs MySQL

SQL is a standard language that stands for Structured Query Language, based on the English language. SQL is the core of the relational database, used for accessing and managing databases.

MySQL is a database management system. MySQL is an RDMS (Relational Database Management System) like SQL Server, Informix, etc.

Subsets of SQL

There are different subsets of SQL, each serving a specific purpose:

• Data Definition Language (DDL): It allows you to perform various operations on the database, such as CREATE, ALTER, and DELETE objects.

• Data Manipulation Language (DML): It allows you to access and manipulate data. It helps you to INSERT, UPDATE, DELETE, and retrieve data from the database.

• Data Control Language (DCL): It allows you to control access to the database. Examples include GRANT and REVOKE access permissions.

• Data Query Language (DQL): The primary statement in this category is SELECT, which is used to query and retrieve data from one or more tables.Other DQL operations include various clauses used with SELECT such as FROM, WHERE, ORDER BY, GROUP BY, HAVING, and more. These clauses help refine and filter the data being retrieved.

Note: The DELETE operation can be considered both a DDL and a DML operation, depending on the context.

Data Definition Language (DDL)

CREATE Statement

The CREATE statement is used to create a table in a database.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

DELETE Statement

The DELETE statement is used to delete records from a table.

DELETE FROM table
WHERE condition;

Note: The DELETE operation can be considered both a DDL and a DML operation, depending on the context.

Data Manipulation Language (DML)

INSERT Statement

The INSERT statement is used to insert data into a table.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

You are inserting values into column1 and column2, and SQL will automatically handle the insertion of default or nullable values for the other columns. If you want to insert values for only some columns, you can specify the column names.

-- Assuming the table has columns: id, column1, column2, column3
-- You want to insert values only for column1 and column2

INSERT INTO your_table (column1, column2)
VALUES (value1, value2);

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

UPDATE table
SET column1 = value1
WHERE condition;

Data Control Language (DCL)

GRANT Statement

The GRANT statement is used to grant access privileges to a user.

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

REVOKE Statement

The REVOKE statement is used to revoke access privileges from a user.

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name};

Data Query Language (DQL)

SELECT Statement

The SELECT statement is used to retrieve data from one or more tables.

SELECT column1, column2
FROM table
WHERE condition;

WHERE Clause

The WHERE clause is used to filter records based on a condition.

SELECT column1, column2
FROM table
WHERE condition;

condition can be any expression that evaluates to true, false, or unknown. The condition can use operators such as =(equal to), != <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), <=> (equal to, including NULL values), BETWEEN (between an inclusive range), LIKE (search for a pattern), IN (specify multiple possible values for a column), and IS NULL (check for NULL values).

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

LEFT OUTER JOIN OR LEFT JOIN

The LEFT OUTER JOIN or LEFT JOIN keyword returns all records from the left table and the matched records from the right table.

SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.column = table2.column;

RIGHT OUTER JOIN OR RIGHT JOIN

The RIGHT OUTER JOIN or RIGHT JOIN keyword returns all records from the right table and the matched records from the left table.

SELECT *
FROM table1
RIGHT OUTER JOIN table2 ON table1.column = table2.column;

FULL OUTER JOIN OR FULL JOIN

The FULL OUTER JOIN or FULL JOIN keyword returns all records when there is a match in either left or right table. The result set contains all rows from both tables, with matched rows showing values from both tables and unmatched rows showing NULL values for columns from the table without a match.

SELECT *
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Note: The FULL OUTER JOIN keyword is supported in some database systems, but not in others. MySQL does not support the FULL OUTER JOIN keyword. However, you can use the UNION operator to combine the results from LEFT and RIGHT joins.

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements.

SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;

UNION ALL

The UNION ALL operator is used to combine the result sets of two or more SELECT statements. It is similar to UNION, but it does not remove duplicate rows.

SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;

SELECT INTO

The SELECT INTO statement is used to create a new table and populate it with the result of a query. It is often used for creating a temporary or summary table based on the data retrieved from an existing table or multiple tables. Example:

SELECT column1, column2
INTO new_table
FROM existing_table;

In this example, new_table is created, and its structure is determined by the columns specified in the SELECT clause.

Aggregate Functions

COUNT

The COUNT function is used to count the number of rows in a result set.

SELECT COUNT(column)
FROM table;

AVG

The AVG function calculates the average value of a numeric column.

SELECT AVG(column)
FROM table;

Window Functions

Window functions operate on a set of rows related to the current row.

ROW_NUMBER()

Assigns a unique integer to each row within a partition.

SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table;

RANK() and DENSE_RANK()

Assigns a rank to each row based on the values in the ORDER BY clause.

SELECT column1, column2, RANK() OVER (ORDER BY column2) AS rank
FROM table;

LEAD() and LAG()

Access data from subsequent or previous rows in the result set.

SELECT column1, LAG(column1) OVER (ORDER BY column2) AS previous_value
FROM table;

FIRST_VALUE() and LAST_VALUE()

Access the first or last value in an ordered set of rows.

SELECT column1, FIRST_VALUE(column1) OVER (ORDER BY column2) AS first_value
FROM table;

PARTITION BY

The PARTITION BY clause divides the rows into groups based on the specified column. The window function is applied to each partition separately and computation restarts for each partition.

SELECT column1, column2, AVG(column2) OVER (PARTITION BY column1) AS avg_column2
FROM table;

LISTAGG Function (Oracle)

The LISTAGG function is an aggregate function in Oracle that concatenates values from multiple rows into a single string. It is often used for creating comma-separated lists or other delimited lists of values.

Example:

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_list
FROM employees
GROUP BY department_id;

In this example, the LISTAGG function is used to concatenate the names of employees within each department, creating a comma-separated list.

Oracle: Using CONNECT BY and REGEXP_SUBSTR

To split a single string into multiple rows in SQL, you can use a combination of string manipulation functions and table-valued functions. The specific approach may vary depending on the database system you are using. Below are examples for two common database systems: Oracle and PostgreSQL.

SELECT 
  TRIM(REGEXP_SUBSTR('John,Mary,Adam', '[^,]+', 1, LEVEL)) AS name
FROM dual
CONNECT BY REGEXP_SUBSTR('John,Mary,Adam', '[^,]+', 1, LEVEL) IS NOT NULL;

In this Oracle example, CONNECT BY with LEVEL is used to generate rows, and REGEXP_SUBSTR is used to extract individual values from the comma-separated string.

PostgreSQL: Using unnest and string_to_array

SELECT unnest(string_to_array('John,Mary,Adam', ',')) AS name;

In this PostgreSQL example, string_to_array is used to split the string into an array, and unnest is used to turn the array back into rows.

Question:

Q: How can you add a primary key to a table when a primary key already exists?

Answer:

If you attempt to add a primary key to a table where a primary key already exists, the database management system will raise an error. A table can have only one primary key, as the primary key uniquely identifies each row in the table.

If you want to modify the existing primary key or add additional constraints, such as unique constraints, you might need to drop the existing primary key first.

If a primary key already exists for a table and you want to modify or add another primary key, you can use the following steps:

1. Add a New Primary Key:

   ALTER TABLE your_table
   ADD COLUMN new_primary_key INT;
   
   UPDATE your_table
   SET new_primary_key = /* logic to generate new primary key values */;
   
   ALTER TABLE your_table
   ADD CONSTRAINT pk_new_primary_key PRIMARY KEY (new_primary_key);
   

2. Optional: Drop the Existing Primary Key (if needed):

   ALTER TABLE your_table
   DROP CONSTRAINT pk_existing_primary_key;
   

3. Note:
   • Ensure that the new primary key values are unique and do not conflict with the existing primary key.
   • Make necessary adjustments based on your specific database system.

Explanation:

When you need to add a new primary key to a table that already has a primary key, you must follow a process that involves adding a new column, updating it with unique values, and then defining it as the new primary key. Optionally, you can drop the existing primary key if needed.