First we will look at some commonly asked questions based on the contents of this page. You can refer to getting-started-with-oracle-xe-and-jdbc-hands-On for setting up oracle xe and database for examples here: create 2 tables employee and emp, with 2 columns empName and id; and insert data with ids ranging from 1 to 5. You might have to use some common sense for some of the examples, like deleting/modifying duplicate data.

Commonly asked questions based on concepts in this page:

1. What does DDL, DML, DCL and TCL stands for? Give examples for each.
2. What is the difference between a Primary Key and Unique constraint?
3. List down few constraints in SQL other than Primary Key and Unique?
4. Differentiate between UNIQUE and DISTINCT?
5. Is SQL case sensitive?
6. How does SQL handle whitespaces?
7. What are SQL expressions? Can we use them within an SQL Select?
8. What are aggregate functions? Give examples.
9. How does an aggregate function in SQL treat NULL?
10. Give the basic usage of ORDER BY and GROUP BY?

Basic Concepts

1. SQL stands for Structured Query Language. SQL is a programming language that lets you communicate with databases to store, manipulate, and retrieve data, and also modify the structure of the database. A database can be considered as a container for tables and a table is a grid with rows and columns to hold data. Individual statements in SQL are called queries.

2. SQL is case-INsensitive. However it is become standard in SQL community to use all capital letters for SQL keywords.

3. In SQL, extra white spaces like spaces, tabs and carriage returns are stripped out before the SQL statement is processed.

4. SQL commands can be divided into DDL, DML, DCL and TCL:

a.     DDL stands for Data Definition Language, and contain commands for creating, modifying and deleting databases, schemas, tables, columns etc. such as CREATE, ALTER, RENAME, DROP etc.

b.     DML stands for Data Manipulation Language, and these queries are used to create or manipulate the actual data. SQL implements CRUD operations through INSERT, SELECT, UPDATE and DELETE commands that belong to DML. CRUD (create, read, update and delete) operations are the four basic functions of any persistent storage mechanisms.

c.      DCL stands for Data Control Language and contain commands such as GRANT, REVOKE etc.

d.     TCL stands for Transaction Control and contain commands such as COMMIT, SAVEPOINT, ROLLBACK etc.

5. Common data types that we can use in SQL are INT, VARCHAR, FLOAT, DATE and TIME. We can also have user defined data types based on predefined SQL types.

6. Different constraints can be used while creating tables in SQL such as UNIQUE, PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, DEFAULT etc.

a.      A PRIMARY KEY is a column or group of columns that represents a unique identifier for each row in a table. PRIMARY key cannot have NULL values.

b.     A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. Also, UNIQUE constraints can accept NULL, but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

c.      A FOREIGN KEY in one table points to a PRIMARY KEY in another table; and is used to specify relationship between tables. The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the PK values in the table it points to.

d.     The CHECK constraint lets you constrain values stored in your table.

e.      DEFAULT constraint gives all fields in a default value.

7. You can use INSERT command to insert data into a table.

INSERT INTO employee values (‘Heartin’,1);

8. We can use INSERT and SELECT together to insert rows from a query. The column names of the columns returned by the SELECT statement are ignored by SQL in an INSERT SELECT statement. So the names of the returned columns do not need to match the column names of the columns data is going into.

INSERT INTO employee SELECT * from emp WHERE id >3;

9. You can use SELECT to retrieve data. You can specify which columns to retrieve in an SQL SELECT statement or use * for all columns. You can also specify an SQL expression, a UNIQUE or DISTINCT, or a combination in a SELECT statement.

a.      SELECT * FROM employee;

b.     SELECT empName, id FROM employee;

c.      SELECT empName, MAX(id) FROM employee;

d.     SELECT empName, CURRENT_DATE FROM employee;

e.      SELECT UNIQUE empName FROM Employees;

f.      SELECT COUNT(DISTINCT lastname) FROM Employees;

10. You can use the UNIQUE and DISTINCT keywords as a field constraint, in the WHERE clause or in the SELECT statement. The difference is that UNIQUE treats two NULL rows as still unique, while DISTINCT treats two NULL rows as NOT UNIQUE. It is better to use DISTINCT as SELECT UNIQUE is considered “old” SQL and your DBMS might not support it.

11. We can use ORDER BY to sort our data and it comes at the end of SELECT statements. The COLUMN you sort on does not need to be returned by SELECT query. We can sort on the value returned by SQL expressions, not just columns. To do a multiple column sort, you specify the columns to sort on, separated by commas. SQL will sort on the columns in the order you give them. Default sort order is ascending. We can make it descending using DESC keyword. 

SELECT columns FROM table_name ORDER BY specifiers DESC;

The keyword for ascending sort is ASC. However since it is default you don’t have to use it.

SELECT columns FROM table_name ORDER BY specifiers;

We can use CASE along with ORDER BY to specify custom sort order.

SELECT * FROM Employees ORDER BY CASE id

WHEN id<1600 THEN 1

WHEN id<1700 THEN 2

END, lastname;

11. An SQL Expression is a term that SQL can evaluate to return a value. Expressions include:

a.      Function calls – These are calls to built-in function.

b.     System values – These include the time, the database user etc.

c.      Numeric or string operators – These include + and – for numeric values and so on.

12. Few built-in functions in SQL are:

a.      SORT() – taking square roots.

b.     LOWER() – returns the lower case.

c.      ABS() – returns the absolute value.

d.     YEAR() – extract year from date.

e.      CONCAT() – joins or concatenates text strings.

f.      MAX() – return a column with largest value.

g.     AVG()

h.     COUNT()

i.       SUM()

13. AVG(), COUNT(), MAX(), MIN(), SUM() etc. are called aggregate functions. NULL values are ignored by these functions.

14. System values are legal to use in place of column names or with function calls in SELECT statements. Few built in system values are:

a.      USER (Current SQL username.)

b.     SESSION_USER (Current SQL session user.)

c.      SYSTEM_USER (Current operating system user.)

d.     CURRENT_DATE

e.      CURRENT_TIME

f.      CURRENT_TIMESTAMP

15. You can use operators to concatenate text strings, but this varies by DBMS. For Example oracle uses a ‘||’ operator to concatenate text strings while SQL server uses ‘+’.

16. We can create groups with the GROUP BY statements. GROUP BY can be used with aggregate functions. For example we can find the number of employees in each department. Many DBMS do not allow grouping based on variable-length fields. You can specify multiple columns in which to group. SQL groups by the first column, then by the second column, then third etc.

SELECT department, COUNT(*) FROM Employees GROUP BY department;

17. You use the UPDATE statement to change data after it is stored in the table, optionally using WHERE clause. You can also update data using fetched data from a query.

a.      UPDATE employee set id=id+1000;

b.     UPDATE employee set id=1001 WHERE id=1;

18. You can use DELETE to delete rows from a table.

DELETE from employee WHERE id=5;

The WHERE clause lets you add further conditions on your query. We will see the use of WHERE clauses along with other clauses that will help us refine our queries in the next section.