Category Archives: SQL

SQL Queries for practice

SQL Queries for practice

Using SELECT

Using SQL Functions

Queries Using Joins

SQL’s using SUBQUERIES

SQL’s using SQL Hierarchy Queries

Psuedo Columns

Set Operators in SQL

Views In Oracle

Indexes

DBA Related Stuff 1

DBA Related Stuff 2

On-line Analytical Processing (OLAP)

Update And Delete

Constraints

SQL Advanced Tables

Object Oriented Database Management System

Insert

PLSQLBasics

 

Thanks Rajesh for Providing the SQL Material.

Related posts:

SQL Practice 1

SQL Practice – 1

If we want to retrieve all of the information about all of the customers in the Employees table, we could use the asterisk (*) as a shortcut for all of the columns, and our query looks like

 

Output:

SQLC1

If we want only specific columns (as is usually the case), we can/should explicitly specify them in a comma-separated list, as in

 

Output:

SQLC2

We want to select only the distinct values from the column named “deptno” from the emp table

 

Ouput:

SQLC3

We want to select only the employees whose working as “Manager” from the emp  table.

 

Ouput:

SQLC4

We could also use the NOT operator, to fetch those rows that are not  between the specified columns.

 

Ouput:

SQLC5

To get a list of employees who were hired on or after a given date, you would write like this

 

Ouput:

SQLC6

We wanted to see if the employees name is”FORD” and ” ALLEN”, we would write

 

Ouput:

SQLC7

This query retrieves data only from those rows of the emp table where the Comm is NULL.

 

Output:

SQLC8

To select employees  whose name begins with ‘S’

 

Output:

SQLC9

It is also possible to select the employees does NOT contain the pattern “S” from the “emp” table, by using the NOT keyword.

 

Ouput:

SQLC10

If you want to sort the emp table by the name, the query would be like,

 

Ouput:

SQLC11

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below

 

Ouput:

SQLC12

If you want to sort the emp table by the sal+nvl(comm.,0), the query would be like,

 

Ouput:

SQLC13

NOTE:The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.

You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name.

The above query can also be written as given below,

 

Ouput:

SQLC14

Related posts:

SPECIAL FILES

SPECIAL FILES

LOGIN.sql

If we would like SQLPLUS to define your own environmental settings, put all the required commands in a file named login.sql. This is a special filename that SQLPLUS always looks for whenever it starts up. If it finds login.sql, it executes any commands in it as if we had entered then by hand. We can put any command in login.sql that we can use in SQLPLUS, including SQLPLUS commands and SQL statements. All of them executed before SQLPLUS gives us the SQL> prompt.

GLOGIN.sql

This is used in the same ways as LOGIN.sql but to establish default SQLPLUS settings for all users of a database.

Related posts: