SQL Query Basics - Beginners

Overview:

I was helping one of my friend on SQL query and thought I can publish the same  as it might be useful for others who are interested in learning SQL Programming

I create this in two phase approach, one for the beginners and one for the advance  users.

Installing Oracle Express Database 11G

You can download the database from the following link
http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

Once you download the ZIP file,  unzip the file and run the setup.exe, keep clicking next button until you reach the step where it'll ask you to setup a password.  Remember the password as this will be your system admin password for  your database

Once the installation is over Install the client utility to run the query, there are several tools the best and free one to me is Oracle SQL Developer, which is available in the same link above under the links section or choose the link below
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Once you complete installation, run the Oracel SQL developer and Connect to the database




You'll see empty connection names on the left hand side  ( I already created few accounts)

Give youself a name to the Conenction Name
choose 'system' for Username
enter the password you created during Oracle 11G Express installation on the  Password section and click 'Test' button and once successful, click 'Connect' button.

Now you'll see your connection in the Navigation pane screen.


by default, Oracle ships with a sample database called HR, but for some reason they lock that account, which we'll unlock in the following steps to start using the sample database.




Expand the 'Other Users' from the navigation pane and
right-click the HR database and select 'Edit User'
un-check all the check boxes and set a password
Click 'Apply'








After, you complete the above step, you are good to go with the HR database.

Now, lets connect to 'HR' database and start exploring our SQL skills.

Select the green '+' sign in the navigation pane or choose 'File>>New>>Database Connection' option from the menu
Enter the following information  on the connection window

Connection Name: Choose your own name or 'HR' in this case
User Name: Choose 'HR'
Password:  The password you choose during unlock section above and click 'Connect'.



Once you are connected you should see the following tables




That's it, lets get our SQL query exercise



Select all columns & rows from a tablE

SELECT * FROM <SCEHMA.TABLE NAME>

• select * from HR.Employees
• select * from HR.Departments

Select total records in a table
SELECT COUT(*) FROM  <SCEHMA.TABLE NAME>

• select count(*) from HR.Employees;

Select specific columns from table
SELECT <COLUMN1>, <COLUMN2>.... FROM <SCEHMA.TABLE NAME>

• SELECT
    EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE
   FROM
    EMPLOYEES ;

Filtering data based on a column (You can use different filter operators (Arithmetic or String)>
SELECT * FROM  <SCEHMA.TABLE NAME> WHERE <COLUMN NAME> condition <VALUE>

• SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1993';

Filtering data based on more than one column

• SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1973' AND SALARY >15000;
•     SELECT * FROM HR.EMPLOYEES where HIRE_DATE >='21-MAR-1973' AND
      SALARY between 10000 and 15000;
• SELECT * FROM HR.EMPLOYEES where DEPARTMENT_ID in (80,100); 

Sorting the data using the 'Order by' clause on a column - Default is Ascending order

• SELECT
    EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
    JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
  FROM
    EMPLOYEES
  ORDER BY FIRST_NAME;

Order by multiple column - Default is Ascending order

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY LAST_NAME, FIRST_NAME;


Order by a column - another way to order without specifying the column name and by specifying the column position in the result

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY 2

Order by a column descending

SELECT
  EMPLOYEE_ID,  FIRST_NAME,  LAST_NAME,  EMAIL,  HIRE_DATE,
  JOB_ID,  SALARY,  MANAGER_ID,  DEPARTMENT_ID
FROM
  EMPLOYEES
ORDER BY FIRST_NAME DESC; 

SELECT
  DEPARTMENT_ID,
  DEPARTMENT_NAME,
  MANAGER_ID,
  LOCATION_ID
FROM
  DEPARTMENTS 
ORDER BY DEPARTMENT_NAME DESC; 


Grouping records by a  particular column

SELECT
  MANAGER_ID,  COUNT(*) "NOofEmployees"
FROM
  EMPLOYEES
  GROUP BY MANAGER_ID

* Note:  "NOofEmplyees" is the alias name for the count(*) results.

Group records by title and add a filter on top of the results

SELECT
  MANAGER_ID,  COUNT(*) "NOofEmployees"
FROM
  EMPLOYEES
  GROUP BY MANAGER_ID
  HAVING COUNT(*) >=8

Please see the next post - Intermediate Queries section  to advance your SQL query skills