Notes of SQL

Dear Friends,

SQL is most common used language in Oracle application. Lets read about it

SQL stands for Structured Query Language and is the standard relational language that is supported by just about every database product. All database professionals should know how to write, troubleshoot, and optimize SQL.

SQL: SELECT STATEMENT

This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples, and practice exercises.

DESCRIPTION

The SQL SELECT statement is used to retrieve records from one or more tables in your SQL database.

SYNTAX

The syntax for the SQL SELECT statement is:

SELECT expressions
FROM tables
WHERE conditions;

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

conditions

The conditions that must be met for the records to be selected.

EXAMPLE - SELECT ALL FIELDS FROM ONE TABLE

Let's look at an example showing how to use the SQL SELECT statement to select all fields from a table.

SELECT *
FROM suppliers
WHERE city = 'Newark'
ORDER BY city DESC;

In this SQL SELECT statement example, we've used * to signify that we wish to view all fields from the suppliers table where the supplier resides in Newark. The result set is sorted by city in descending order.

EXAMPLE - SELECT INDIVIDUAL FIELDS FROM ONE TABLE

You can also use the SQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT supplier_name, city, state
FROM suppliers
WHERE supplier_id > 1000
ORDER BY name ASC, city DESC;

This SQL SELECT example would return only the supplier_name, city, and state fields from the suppliers table where the supplier_idvalue is greater than 1000. The results are sorted by supplier_name in ascending order and then city in descending order.

EXAMPLE - SELECT FIELDS FROM MULTIPLE TABLES

You can also use the SQL SELECT statement to retrieve fields from multiple tables.

SELECT orders.order_id, suppliers.name
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY order_id;

This SQL SELECT example joins two tables together to gives us a result set that displays the order_id and supplier name fields where the supplier_id value existed in both the suppliers and orders table. The results are sorted by order_id in ascending order.

Learn more about SQL joins.

PRACTICE EXERCISE #1:

Based on the employees table below, select all fields from the employees table whose salary is less than or equal to $52,500 (no sorting is required):

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

Solution for Practice Exercise #1:

The following SQL SELECT statement would select these records from the employees table:

SELECT *
FROM employees
WHERE salary <= 52500;

PRACTICE EXERCISE #2:

Based on the suppliers table below, select the unique city values that reside in the state of Florida and order the results in descending order by city:

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  state varchar2(25),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Solution for Practice Exercise #2:

The following SQL SELECT statement would select these records from the suppliers table:

SELECT DISTINCT city
FROM suppliers
WHERE state = 'Florida'
ORDER BY city DESC;

PRACTICE EXERCISE #3:

Based on the suppliers table and the orders table below, select the supplier_id and supplier_name from the suppliers table and select the order_date from the orders table where there is a matching supplier_id value in both the suppliers and orders tables. Order the results by supplier_id in descending order.

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  state varchar2(25),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE orders
( order_id number(10) not null,
  supplier_id number(10) not null,
  order_date date not null,
  quantity number(5),
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Solution for Practice Exercise #3:

The following SQL SELECT statement would select these records from the suppliers and orders table (using a SQL INNER JOIN):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
ORDER BY supplier_id DESC;

PRACTICE EXERCISE #4:

Based on the customers and old_customers table, select the customer_id and customer_name from the customers table that exist in the old_customers table (matching the customer_id field from the customers table to the old_customer_id field in the old_customerstable). Order the results in ascending order by customer_name and then descending order by customer_id.

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE old_customers
( old_customer_id number(10) not null,
  old_customer_name varchar2(50) not null,
  old_city varchar2(50),
  status varchar2(20),
  CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id)
);

Solution for Practice Exercise #4:

The following SQL SELECT statement would select the records from the customers table (using the SQL EXISTS clause):

SELECT customer_id, customer_name
FROM customers
WHERE EXISTS
  ( SELECT old_customers.old_customer_id
    FROM old_customers
    WHERE old_customers.old_customer_id = customers.customer_id )
ORDER BY customer_name ASC, customer_id DESC;

Or alternatively you could exclude the ASC keyword for customer_name in the ORDER BY clause. Both of these SELECT statements would generate the same results:

SELECT customer_id, customer_name
FROM customers
WHERE EXISTS
  ( SELECT old_customers.old_customer_id
    FROM old_customers
    WHERE old_customers.old_customer_id = customers.customer_id )
ORDER BY customer_name, customer_jd DESC;