SQL Advance

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;

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%';

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;

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;

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;

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;

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

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.

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;

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;

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;

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 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';

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