SQL Advance

By ukmodak | June 6th 2022 12:23:03 PM | viewed 237 times

SQL Advance

Type Description Query
The UNION Operator The UNION operator is used to combine the results of two or more SELECT queries into a single result set. The union operation is different from using joins that combine columns from two tables. The union operation creates a new table by placing all rows from two source tables into a single result table, placing the rows on top of one another.

These are basic rules for combining the result sets of two SELECT queries by using UNION:

  • The number and the order of the columns must be the same in all queries.
  • The data types of the corresponding columns must be compatible

SELECT first_name, last_name FROM employees UNION SELECT first_name, last_name FROM customers;

SELECT first_name, last_name FROM employees UNION ALL SELECT first_name, last_name FROM customers;

LIKE Operator So far, you've seen the conditions that identify an exact string, e.g. WHERE name='Lois Lane'. But in SQL you can perform partial or pattern matching too using the LIKE operator.

The LIKE operator provides a measure of pattern matching by allowing you to specify wildcards for one or more characters. You can use the following two wildcard characters:

  • The percent sign (%) — Matches any number of characters, even zero characters.
  • The underscore (_) — Matches exactly one character

SELECT * FROM employees WHERE emp_name LIKE 'S%';

Statement Meaning Values Returned
WHERE name LIKE 'Da%' Find names beginning with Da David, Davidson
WHERE name LIKE '%th' Find names ending with th Elizabeth, Smith
WHERE name LIKE '%on%' Find names containing the on Davidson, Toni
WHERE name LIKE 'Sa_' Find names beginning with Sa and is followed by at most one character Sam
WHERE name LIKE '_oy' Find names ending with oy and is preceded by at most one character Joy, Roy
WHERE name LIKE '_an_' Find names containing an and begins and ends with at most one character Dana, Hans
WHERE name LIKE '%ar_' Find names containing ar, begins with any number of characters, and ends with at most one character Richard, Karl
WHERE name LIKE '_ar%' Find names containing ar, begins with at most one character, and ends with any number of characters Karl, Mariya
Table Aliases When multiple tables are being joined in a single query, you need to prefix each column name with the name of the table it belongs to, like employees.dept_id, departments.dept_id, etc. in order to avoid the confusion and ambiguous column error in case columns in different tables have the same name. But, if table names are long and appears several times in the query then writing the query would become a difficult and annoying task.

SELECT t1.emp_id, t1.emp_name, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id ORDER BY emp_id;

SELECT emp_name, DATE_FORMAT(hire_date, '%M %e, %Y') AS hire_date FROM employees;

GROUP BY Clause

The GROUP BY clause is used in conjunction with the SELECT statement and aggregate functions to group rows together by common column values

Now, let's say instead of finding just name of the employees and their departments, you want to find out the total number of employees in every department.

In case of small tables you can simply apply the left join and count the number of employees, but suppose if a table contains thousands of employees then it wouldn't be so easy.

In this situation you can use the GROUP BY clause with the SELECT statement, like this:

SELECT t1.dept_name, count(t2.emp_id) AS total_employees FROM departments AS t1 LEFT JOIN employees AS t2 ON t1.dept_id = t2.dept_id GROUP BY t1.dept_name;

SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;

Creating Views to Simplify Table Access

A view is a virtual table whose definition is stored in the database. But, unlike tables, views do not actually contain any data. Instead, it provides a way to store commonly used complex queries in the database. However, you can use the view in a SQL SELECT statement to access the data just as you would use a normal or base table.

Views can also be used as a security mechanism by allowing users to access data through the view, rather than giving them direct access to the entire base tables.

CREATE VIEW emp_dept_view AS SELECT t1.emp_id, t1.emp_name, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id;

SQL HAVING Clause

The HAVING clause is typically used with the GROUP BY clause to specify a filter condition for a group or an aggregate. The HAVING clause can only be used with the SELECT statement.

Now, let's say instead of finding just name of the employees and their departments, you want to find out the names of those departments in which there are no employees.

SELECT t1.dept_name, count(t2.emp_id) AS total_employees FROM departments AS t1 LEFT JOIN employees AS t2 ON t1.dept_id = t2.dept_id GROUP BY t1.dept_name HAVING total_employees = 0;

Index

An index is a data structure associated with a table that provides fast access to rows in a table based on the values in one or more columns (the index key).

Let's say, you have a customers table in your database and you want to find out all the customers whose names begin with the letter A, using the following statement.

To find such customers, server must scan each row one by one in the customers table and inspect the contents of the name column. While it works fine for a table having few rows, but imagine how long it might take to answer the query if the table contains million of rows. In such situation you can speed things up by applying indexes to the table.

CREATE INDEX cust_name_idx ON customers (cust_name);

CREATE UNIQUE INDEX cust_name_idx ON customers (cust_name);

SHOW INDEXES FROM customers

SQL Dates and Times

Along with strings and numbers, you often need to store date and/or time values in a database, such as an user's birth date, employee's hiring date, date of the future events, the date and time a particular row is created or modified in a table, and so on.

This type of data is referred as temporal data and every database engine has a default storage format and data types for storing them. The following table shows the data types supported by the MySQL database server for handling the dates and times.

Type Default format Allowable values
DATE YYYY-MM-DD 1000-01-01 to 9999-12-31
TIME HH:MM:SS or HHH:MM:SS -838:59:59 to 838:59:59
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 to 2037-12-31 23:59:59
YEAR YYYY 1901 to 2155

INSERT INTO users (name, birth_date, created_at) VALUES ('Bilbo Baggins', '1998-04-16', NOW());

SELECT name, YEAR(birth_date) FROM users;

SELECT name, DAYOFMONTH(birth_date) FROM users;

SQL Cloning Tables

There may be a situation when you just want to create an exact copy or clone of an existing table to test or perform something without affecting the original table

This type of data is referred as temporal data and every database engine has a default storage format and data types for storing them. The following table shows the data types supported by the MySQL database server for handling the dates and times.

CREATE TABLE employees_clone LIKE employees;

INSERT employees_clone SELECT * FROM employees;

CREATE TABLE new_table SELECT * FROM original_table;

CREATE TABLE new_table SELECT * FROM original_table;

SQL Temporary Tables

A temporary table is a table that is visible only to the current session, and is dropped automatically when the session in which it was created is closed.

Since temporary tables are not stored in the database on a permanent basis, therefore, it would be useful in a situation where you need a table only for a short time to perform or test something, after which you want it to disappear automatically.

CREATE TEMPORARY TABLE persons SELECT * FROM persons;

DROP TEMPORARY TABLE persons;

SQL Subqueries

A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions. The most important ones are listed below:

  • A subquery must always appear within parentheses.
  • A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.
  • You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.
  • A subquery cannot be a UNION. Only a single SELECT statement is allowed.

SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value > 5000);

INSERT INTO premium_customers SELECT * FROM customers WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value > 5000);

UPDATE orders SET order_value = order_value + 10 WHERE cust_id IN (SELECT cust_id FROM customers WHERE postal_code = 75016);

DELETE FROM orders WHERE order_id IN (SELECT order_id FROM order_details WHERE product_id = 5);

SQL Injection

SQL injection is an attack wherein an attacker can inject or execute malicious SQL code via the input data from the browser to the application server, such as web-form input.

It can be used to expose sensitive information like user's contact numbers, email addresses, credit card information and so on. An attacker can even use it to bypass authentication process and get access to the entire database. Let's see how it actually works.

SELECT * FROM users WHERE username='' OR 'x'='x' AND password='' OR 'x'='x'; instead of SELECT * FROM users WHERE username='john' AND password='123';

Preventing SQL Injection

$username_val = mysqli_real_escape_string($link, $_POST['username']);

bONEandALL
Visitor

Total : 18980

Today :9

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia