SQL and No SQL - Yash-777/MyWorld GitHub Wiki

SQL Questions

-- Second highest record
SELECT first_name, MAX(age) FROM Customers 
    WHERE age < (SELECT MAX(age) FROM Customers);

select MAX(age) from Customers 
    where age NOT IN ( select MAX(age) from Customers );

SELECT MAX(salary) as second_highest_salary FROM employees
    WHERE salary < ( SELECT MAX(salary) FROM employees );

-- The orders the result set in descending order and selects the top 2 rows, which correspond to the second and third-highest salaries.
SELECT salary FROM employees 
    WHERE salary NOT IN ( SELECT MAX(salary) FROM employees )
ORDER BY salary DESC
LIMIT 2; -- Third highest using limit and sub-query

-- Emp highest sal in each department  Emp[name, department, sal]
-- SYNTAX: SELECT colunm_name, MAX(column_name) FROM table_name GROUP BY column_name;
SELECT DEPT_ID, MAX(SALARY) FROM department GROUP BY DEPT_ID;

-- Assuming you have an "employee" table with columns "id", "first_name", "middle_name", and "last_name", you can read the first, middle, and last names
-- of employees whose middle name contains the substring "kumar" as a single column
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name FROM employee
    WHERE middle_name LIKE '%kumar%';

Introduction to Databases and SQL programiz.com

A database is an organized collection of data so that it can be easily accessed. To manage these databases, Database Management Systems (DBMS) are used.

Types of DBMS

In general, there are two common types of databases:

  • Non-Relational
  • Relational
Non-Relational Database Management System (Non-RDBMS) Relational Database Management System (RDBMS)
In Non-RDBMS, data is stored in key-value pairs.
Commonly used Non-RDBMS: MongoDB, Amazon DynamoDB, Redis, etc. 		In RDBMS, data is stored in tabular format.
Commonly used RDBMS: MySQL, PostgreSQL, MSSQL, Oracle etc.
The first row is the attributes of the table. Each row after that contains the data of a customer.

In RDBMS, two or more tables may be related to each other. Hence the term "Relational". For example,
image image

Tables: Customers, Orders, Shippings

customer_id first_name last_name age country order_id item amount customer_id shipping_id status customer
1 John Doe 31 USA 1 Keyboard 400 4 1 Pending 2
2 Robert Luna 22 USA 2 Mouse 300 4 2 Pending 4
3 David Robinson 22 UK 3 Monitor 12000 3 3 Delivered 3
4 John Reinhardt 25 UK 4 Keyboard 400 1 4 Pending 5
5 Betty Doe 28 UAE 5 Mousepad 250 2 5 Delivered 1

Note: To access data from these relational databases, SQL (Structured Query Language) is used.

CREATE DATABASE/TABLE, DROP DATABASE/TABLE, ALTER TABLE

A database table is used to store records (data). To create a database table, we use the SQL CREATE TABLE statement.

CREATE DATABASE IF NOT EXISTS my_db;
SHOW DATABASES; -- List all Databases
USE my_db;      -- To switch between available databases

DROP DATABASE my_db;

DROP TABLE my_table;   -- DROP TABLE IF EXISTS my_table;

CREATE TABLE Companies (
  id int,
  name varchar(50),
  address text,
  email varchar(50),
  phone varchar(10)
);

We can change the structure of a table using the ALTER TABLE command. We can Alter on different DB servers

  • Add a column
  • Rename a column
  • Modify a column
  • Delete a column
  • Rename a table
ALTER TABLE Customers
    ADD phone varchar(10);

ALTER TABLE Customers
    RENAME COLUMN customer_id TO c_id;

ALTER TABLE Customers
    MODIFY COLUMN age VARCHAR(2);

ALTER TABLE Customers
    DROP COLUMN age;

ALTER TABLE Customers
    RENAME TO newCustomers;

INSERT, UPDATE, Delete, Truncate rows

INSERT INTO Customers(first_name, last_name, age, country)
  VALUES
    ('Harry', 'Potter', 31, 'USA'),
    ('Chris', 'Hemsworth', 43, 'USA'),
    ('Tom', 'Holland', 26, 'UK');

update Orders
    set customer_id = 2
    where order_id = 1;

DELETE FROM Customers; -- deletes all rows from a table.
DELETE FROM Customers
    WHERE customer_id = 5; -- deletes condition satisfying rows

TRUNCATE TABLE Customers; -- delete all rows from a table at once.

SQL LIMIT, LIMIT With OFFSET and ORDER BY Clause

The LIMIT keyword is used with the following database systems:

  • MySQL
  • PostgreSQL
  • SQLite

The OFFSET keyword is used to specify starting rows from where to select rows.

-- Descending top 3 records
select first_name, age from Customers 
    order by age 
    desc limit 0, 3; -- 0,1,2 decending records
-- Second highest record
select first_name, age from Customers 
    ORDER BY age 
    desc limit 1, 1; -- 1 decending records

-- Second highest record
SELECT first_name, MAX(age) FROM Customers 
    WHERE age < (SELECT MAX(age) FROM Customers);

select MAX(age) from Customers 
    where age NOT IN ( select MAX(age) from Customers );

SQL (IN, NOT, NOT IN) (BETWEEN, NOT BETWEEN) (LIKE and NOT LIKE with Wildcards %, _, [], !) Operator

The IN operator is used with the WHERE clause to match values in a list.

The NOT IN operator returns is used to exclude the rows that match values in the list. It returns all the rows except the excluded rows.

SELECT first_name, country FROM Customers
    WHERE country IN ('USA', 'UK');

SELECT first_name, country FROM Customers
    WHERE country NOT IN ('UK', 'UAE');

SELECT last_name, age FROM Customers
    WHERE NOT country = 'UK'
    ORDER BY last_name DESC;

SQL IN Operator With Subquery: Suppose we want details of customers who have placed an order. Here's how we can do that using a subquery,

SELECT customer_id, first_name FROM Customers 
    WHERE customer_id NOT IN (
      SELECT customer_id FROM Orders
    );
customer_id first_name
5 Betty 
SELECT customer_id, first_name FROM Customers 
    WHERE customer_id IN (
      SELECT customer_id FROM Orders
    );
customer_id first_name
1 John
2 Robert
3 David
4 John

The BETWEEN operator is used with the WHERE clause to match values in a range.

The NOT BETWEEN operator is used to exclude the rows that match values in the range. It returns all the rows except the excluded rows.

SELECT item, amount FROM Orders
    WHERE amount BETWEEN 300 AND 500;

SELECT item, amount FROM Orders
    WHERE amount NOT BETWEEN 300 AND 500;

SQL Wildcards %, _, [], !

Here, % (means zero or more characters) is a wildcard character.

The _ wildcard in SQL is used to represent exactly one character in a string.

The [] wildcard in SQL is used to represent any one character inside brackets.

The ! wildcard in SQL is used to exclude characters from a string.

SELECT * FROM Customers
    WHERE country LIKE 'US%';

SELECT * FROM Customers
    WHERE country NOT LIKE '%USA';

SELECT * FROM Customers
    WHERE country LIKE 'U_';
    
SELECT * FROM Customers
    WHERE country LIKE 'U[KA]%';
    
SELECT * FROM Customers
    WHERE last_name LIKE '[!DR]%';

SQL IS NULL and IS NOT NULL

Employee table

emp_id first_name last_name department email
1 Peter Doe Operations [email protected]
2 Megan Morel Finance  
3 Rose Bailey Finance [email protected]
4 Linda Bailey Finance  
5 Mary Doe Sales 
SELECT * FROM Employee
    WHERE email IS NULL;

SELECT * FROM Employee
    WHERE email IS NOT NULL;
 
SELECT COUNT(*) FROM Employee
    WHERE email IS NULL;

SQL Aggregate Function MAX(), MIN(), Count(), SUM(), AVG() 

SELECT MAX(age) FROM Customers;
SELECT MIN(age) FROM Customers;
   
SELECT COUNT(country) FROM Customers;
SELECT COUNT(DISTINCT country) FROM Customers;

SELECT SUM(amount) AS total_sales FROM Orders; --13350
SELECT AVG(amount) AS total_sales FROM Orders; --2670

GROUP BY With HAVING Clause, ORDER BY 

SELECT * FROM Customers
    GROUP BY country HAVING MAX(age);
    
SELECT * FROM Customers
    ORDER BY age desc limit 0, 1;

Note: The WHERE clause must appear before the ORDER BY clause, while using the WHERE clause with ORDER BY.

UNION, UNION ALL UNION Vs UNION ALL

To use UNION in SQL, we must always remember,

  • Column count in all tables must be the same. For example, Teachers and Students both tables have three columns.
  • The data types of columns must be the same. For example, the age column in Teachers is integer, so is the age in Students table.
  • The columns must be in the same order in each table. For example, the order of columns is id-name-age in Teachers, so in the Students table.

UNION allows doesn't allow duplicate records/result. UNION ALL allow duplicates
UNION ALL: Executes fast as there is no need to filter the result-sets by removing duplicate values.

Students id name age Teachers id name age
1 Harry 23 1 Peter 32
2 Jack 45 2 Megan 43
3 Joe 32 3 Rose 29
4 Dent 23 4 Linda 30
5 Bruce 40 5 Mary 41 
SELECT age FROM Teachers
UNION
SELECT age FROM Students
    ORDER BY age DESC;  -- Not Allows Duplicates

SELECT age FROM Teachers
UNION ALL
SELECT age FROM Students
    ORDER BY age DESC;  -- Allow Duplicates

SQL Subquery

In SQL, it's possible to place a SQL query inside another query known as subquery.

SELECT * FROM Customers
  WHERE age = (
    SELECT MIN(age) FROM Customers
  );

Note: It's preferred to use the JOIN clause instead of a subquery whenever possible. It's because the execution speed of JOIN is faster and more optimized than a subquery.

JOIN

The SQL JOIN joins two tables based on a common column, and selects records that have matching values in these columns.

Types of SQL JOINs

The JOIN command we performed earlier is INNER JOIN. There are mainly four types of joins.

Select * from Students s
  JOIN Teachers t
    ON s.age = t.age;
    
Select * from Students s
  LEFT JOIN Teachers t
    ON s.age = t.age;

Select * from Students s
  RIGHT JOIN Teachers t
    ON s.age = t.age;

Select * from Students s
  FULL OUTER JOIN Teachers t
    ON s.age = t.age;

Update copilot.microsoft.com

Updating data in a MySQL table using a subquery. Vehicle Number Plates change from TS to TG

MySQL Error Code: 1093. You can't specify target table 'Title' for update in FROM clause: The MySQL error code 1093 occurs when you try to update a table using a subquery that references the same table in the FROM clause.

UPDATE `Title` SET `NextTitle_Id` = (select id from Title where code = 'ADV') WHERE (`Code` = 'TL')
-- Solution
UPDATE Title AS t1 JOIN Title AS t2 ON t2.Code = 'TL' SET t1.NextTitle_Id = t2.id WHERE t1.Code = 'ADV';
    Workarounds to overcome this limitation:
  1. Join the Table to Itself: Instead of using a subquery, join the table to itself. This way, MySQL treats them as separate entities. 
    UPDATE Title AS t1
 JOIN Title AS t2 ON t1.Code = 'STL'
   SET t1.SucceedingTitle_Id = t2.id
 WHERE t1.Code = 'ADV';
  2. Nested Subquery in the FROM Clause: Create a nested subquery in the FROM clause to create an implicit temporary table. Example (not recommended for large datasets due to performance): 
    UPDATE Title
SET SucceedingTitle_Id = (
    SELECT id FROM (SELECT id FROM Title WHERE code = 'ADV') AS x
)
WHERE Code = 'STL';
  3. Adjust Optimizer Behavior (Not Recommended): Starting from MySQL 5.7.6, the optimizer may still optimize out the subquery even with the nested subquery approach. You can disable this behavior using the optimizer_switch variable, but it’s not recommended for long-term use: 
    SET optimizer_switch = 'derived_merge=off';
UPDATE Title
SET SucceedingTitle_Id = (
    SELECT id FROM (SELECT id FROM Title WHERE code = 'ADV') AS x
)
WHERE Code = 'STL';
Sample Code 




Full Code













⚠️ **GitHub.com Fallback** ⚠️