2. SELECT Statements
Introduction to Oracle SQL
Structured Query Language (SQL) also called SEQUEL, is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user’s request.
It was developed by Dr. E. F. Codd. SQL is accepted as the standard RDBMS language.
https://ohiocomputeracademy.com/what-are-the12-codd-rules-for-rdbms/
SQL provides statements for a variety of tasks, including:
SQL unifies all of the preceding tasks in one consistent language.
SQL and Sub languages:
SQL is made of three sub-languages such as:
Data Manipulation Language (DML): Used for query, insertion, deletion and updation of data stored in the databases.
Data Definition Language (DDL): Commands to create objects such as tables and views.
Data Control Language (DCL): Used for controlling data and access to the database.
Installation
To follow this course with hands-on practice, you need to install Oracle.
To run query’s, you also need to install, SQL developer. Google and download the installation files.
Data Types in Oracle
Each column value and constant in a SQL statement has a data type, which is associated with a specific storage format, constraints, and a valid range of values.
When a table is created, a data type is specified for each of its columns. Oracle provides the following built-in data types.
| DataType | Description |
| CHAR (size) | Fixed-length character data of length size bytes. |
| VARCHAR2 (size) | Variable-length character data. |
| NCHAR(size) | Fixed-length character data of length size characters or bytes, depending on the national character set. |
| NVARCHAR2 (size) | Variable-length character data depending on national character set. |
| CLOB | Stands for character large objects, used to store string data too large to be stored in a VARCHAR column |
| NCLOB | Single-byte or fixed-length multibyte national character set (NCHAR) data. |
| LONG | Variable-length character data. |
| NUMBER (p, s) | Variable-length numeric data. Maximum precision p and/or scale s is 38. |
| DATE | Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E. |
| BLOB | BLOB stands for binary large objects, which are used for storing binary data, such as an image. |
| BFILE | Used to store a locator (link) to an external binary file (file stored outside of the database). |
| RAW (size) | Used to store binary data, (for example, graphics or audio files). It can only be queried or inserted; RAW data cannot be manipulated. |
| LONG RAW | Variable-length raw binary data. |
| ROWID | Binary data representing row addresses. |
Queries
Query’s are written using SQL Developer. To write a query, invoke SQL Developer and connect to the appropriate scheme or database. (Sometimes these terms are used interchangeably)
To connect to a database or schema you will need the appropriate user id and password.
*The queries in this section follows Scott/tiger sample database. Scott is the database user name and tiger is the password.
Select Statement
A query, or SQL SELECT statement, selects data from one or more tables or views. A query always starts with a Select statement.
A query will always have a SELECT statement.
The simplest form of query has this syntax:
SELECT <Column1, Column2,.. Column n> FROM <Table name1, Table name2..Table name n>;
A SELECT statement can be extended to include other statements or clauses,
SELECT <Column-list> FROM <table-name>
WHERE <condition>
GROUP BY <column-name(s)>
HAVING <condition>
ORDER BY <expression>;
The columns/fields from which the data is to be selected, and the Table name specifies the tables or views that have these columns.
Tip: Semicolon (;) at the end, specifies end of the statement. Also check the spelling of the Column names and table names. They should be spelled exactly as in your database table.
To check the columns in your table, use desc <tablename> or collapse your table in SQL developer.
Rules for writing SQL Statements
- SQL statements are not case sensitive.
- SQL statements can be on one or more lines.
- Keywords cannot be abbreviated or split across lines.
- Clauses are usually placed on separate lines.
- Indents are used to enhance readability.
Ques. Select all columns from Table EMP
- Launch SQL developer. Check you are connected to Scott/tiger database.
- In the SQL worksheet, type the following statement
SELECT * FROM EMP;
- Click Execute button
- Check the results. See the number of rows fetched and time taken to fetch the results.
Ques. Select all columns from Table DEPT;
SELECT * FROM DEPT;
Ques. How many rows of data is there in Table SALGRADE;
SELECT * FROM SALGRADE;
Ques. How many columns are there in Bonus table and how many rows of data;
<Student should provide the answer>
*You can select specific columns/fields from a table.
Ques. Select empno and ename from table EMP:
SELECT empno, ename
FROM EMP ;
Ques. Display DeptNo and Dname from the table Dept:
SELECT DEPTNO, Dname
FROM DEPT;
Ques. Display Employee names and their Salaries:
SELECT ename, Sal
FROM EMP;
Ques. List all the department numbers, employee numbers and their Manager numbers from the EMP table.
<Student should provide the answer>
Ques. List department names and locations from a table in the database.
<Student should provide the answer>
Using an Alias – renaming a column in the display
When query is displayed, the default column headings in the tables are displayed. Sometimes, in real life scenarios you are may have to display these returned columns with a different or a new name.
You can do so my specifying an alias immediately after the column name. This only changes the display column name and DOES NOT change the name of the column in the table.
Ques. Select ename, Job and DeptNo from EMP table.
Display ename as Name, Job as Position and DeptNo as Department_ID
SELECT ename as Name, Job as Position, DeptNo as Department_ID
FROM EMP;
Tip: Check the results. All alias columns are displayed in caps. To change this use double quotes around them.
SELECT ename as "Name", Job as "Position", DeptNo as "Department_ID"
FROM EMP;
Tip: As is optional but a good practice. Try above query without ‘as’ and verify the results
Practice the above alias statements with other columns/tables. Try to get a space between the alias columns like “Department ID”.
Sorting Selected Data – Order By Clause
When the results of a query are displayed, records or the output can be in any order, unless an order is specified using an ORDER BY clause. Default sort order of an Order BY clause is Ascending.
Ques. Select empno, ename, HireDate, Sal from Emp and sort the results by ename.
SELECT empno, ename, HireDate, Sal
FROM Emp
ORDER BY ename;
Ques. Selectempno, ename, HireDate, Sal from Emp and sort the results by empno.
SELECT empno, ename, HireDate, Sal
FROM Emp ORDER BY empno;
Using ascending (ASC) and descending (Desc) with Order By Clause
Ques. Selectempno, ename, HireDate, Sal from Emp and sort the results by Sal in descending Order.
SELECT empno, ename, HireDate, Sal
FROM Emp ORDER BY Sal Desc;
Practice: Practice the above statement with ASC and
Write above query by using number instead of ‘Sal’ in the Order By
Eliminating the duplicate rows – Distinct
Sometimes you get duplicate rows in your query, check by selecting Department_Id from Employees. To eliminate the duplicate rows, use Distinct Keyword.
Ques. Display the all the unique department numbers in the Employees table. (Before doing this, complete below parts- a, b)
- First check if you get duplicate departments in Emp table. Answer WHY you are getting duplicates.
- Check if you are getting duplicates Department numbers in Dept table. If yes then WHY. If no then WHY.
SELECT Distinct Department_Id
FROM Emp;
Selecting Data based on a condition – Where Clause
We don’t always query the tables directly for all the values, this is especially true when we are looking for a specific value or when the data in the table is huge. The Where clause can also be used as a filter to the query.
Conditions can also be implemented by using the relational and Logical operators with WHERE.
Ques. Display the details of the Employees working in Department 20.
SELECT *
FROM emp
WHERE DeptNo = 20;
Ques. Display the Employee number and Names of Employees whose job description is Manager.
SELECT empno, ename
FROM emp
WHERE Job = 'MANAGER';
Practice: In the above query, can you use Job = ‘Manager’ and check the result.
Ques. List all the Clerks in the company.
<Student should provide the answer>