Mastering SQL for Data Science: A Comprehensive Guide Day-2

Mastering SQL for Data Science: A Comprehensive Guide Day-2

SQL Commands

Ø  DDL (Data Definition Language) commands:

ü  Create table command:

1)     A name can have maximum up to 30 characters.

2)     Alphabets from A..Z and a..z or numbers from 0..9 are allowed.

3)     A name should starts with an alphabet.

4)     The use of a special character like _ is allowed.

5)     SQL reserved words are not allowed.

ü  Desc command:

Ø  DML (Data Manipulation Language) command:

·    Insert Command:

After creation of a table, it is necessary it should have data in it. The insert command is used to add data in form of one or more rows to a table as per follows;

Syntax: insert command for 1 record
Insert into <table_name> (<column_name1>,<column_name2>…) Values (<value1>,<value2>……);

Till now we have seen static method to insert data. One can add data in a table using variable method with & sign. It will prompt user to enter data of mention field. Generally, it is used to add more than one row in a table without typing whole command repetitively using / sign.

Syntax: insert command for multiple records
Insert into <table_name> (<column_name1>,<column_name2>…)
Values (‘&<column_name1>’,’&<column_name2>’……)’

 

·    Update Command:

Sometimes changes should be required in existing data to reflect such changes to the existing records in a table, update command is used. We can update single column or more than one columns. Specific rows could be updated based on some condition. If condition is not specified with where clause it will update all the rows of specified column.

Syntax: update all rows

Update <table_name> set <column_name>=<expression>,

<column_name2>=<expression>;

Syntax: Update specific rows only

update <table_name> set <column_name>=<expression>

  Where <condition>;

 

·       Delete Command:

To delete any inserted records, delete command is used. Delete command can also be used with where condition to delete any specific rows. If where condition is not specified then it will delete all the rows but structure remains as it is. 

Syntax: Delete specific rows only

delete from <table_name> where <condition>;

 Syntax: Delete all the rows

delete from <table_name>;

 Ø  Select command with different clauses 

• Select with where clause:

We can easily search any specific record through the select command with where clause.

E.g: select emp_no,name from d_emp where sal>20000;

 

• Select with distinct clause:

This clause is useful to display unique data from any specific column.

E.g: select distinct city from d_emp;

 

• Select with order by clause:

This clause is useful to display all records of any table in ascending or descending form. Remember that this result is temporary. It will not permanently change in the table.

E.g: select * from d_emp order by name;

 Ø  Null concept:
Often there may be records in a table that do not have values for every field. This could be because the information is not available at the time of data entry or because the field is not applicable in every case. If the column was also created as nullable, oracle will place a null value in the absence of a user defined value.
A Null value is different from a blank or a zero. A null value can be inserted into columns of any data type. 

Principles of Null values:

1. Setting a NULL value is appropriate when the actual value is unknown or when a value would not be meaningful.
2. A Null value is not equivalent to a value of zero if the data type
   is number and is not equivalent to spaces if the data type is varchar/character.
3. Null value can be inserted into columns of any data type.
4. If the column has a null value, oracle ignores any unique, foreign key constraints or check constraints that may be attached to the column.

E.g: retrieve all rows where the job contains a null value.

Select * from d_emp where job is null;

 

Ø  SQL Operators:

1.    Arithmetic operator:

Oracle allows arithmetic operators to be used while viewing records from a table or while performing data manipulation operators such as insert, update and delete.
These are;

+   Addition
–    Subtraction

*   Multiplication
/    Division

Renaming columns or set alias of any columns used with expression
Syntax:     Select <columnname><aliasname> from <tablename>;

E.g:     Select (basic+da) netsal from employee;

 

2.    Comparison operator:

Comparison operators are used in condition to compare one expression with another. These operators are nearly similar to the relational operators. Operators are <, >, <=,>=,!=,= and also like, between, in, not in, any, all and exists.

E.g: select * from employee where sal<3000;

 ·        Like operator (Pattern Matching):

The comparison operators discussed so far have compare one value exactly to another value. Such precision may not always be desired or necessary. For this special purpose oracle provides the like predicate. This operator is used to search character pattern, we need not know the exact character values. Two wildcard characters those are available;

% – allows to match any string of any length.

_  – allows to match a single character.

E.g: 1) select * from employee where ename like ‘A%’;

      2) select * from employee where city like ‘_a%’;

 ·        Between operator (Range Searching):

In order to select data that is within a range of values, the between operator is used. The between operator allows the selection of rows that contain values within a specified lower and upper limit. The between operator can be used with both character and numeric data types.

E.g: select * from employee where sal between 2000 and 5000;

 

·    In operator:

The arithmetic operator(=) compares a single value to another value. In case a values needs to be compared to a list of values then the in predicate is used. This operator helps reduce the need to use multiple or conditions. 

E.g: retrieve all information of clients whose names in tanush, jay, Manyata

Select * from client where cname in (‘tanush’,’jay’,’Manyata’);

 

• Not in operator:

The not in operator is the opposite of the in operator. This will select all the rows where values do not match the values in the list. 

E.g: retrieve all information of clients other than  tanush, jay, Manyata.

Select * from client where cname not in (‘tanush’,’jay’,’Manyata’);

 

3.    Logical operator:

Logical operators that can be used in sql sentences are;

·    And operator:

The and operator allows creating an sql statement based on two or more conditions begin met. It can be used in any valid sql statement such as select, insert, update or delete. The and operator requires that each condition must be met for the record to be included in the result set. 

E.g: retrieve information about employees who are clerk and having salary 4000.

Select * from employee where job=’clerk’ and sal=4000;

 

• Or operator:

The or condition allows to creating an sql statement where records are returned when any one of the conditions are met. It can be used in any valid sql statements such as select, insert, update or delete.

The oracle engine will process all rows in a table and display the result only when any of the condition specified using the or operator is satisfied.

E.g: retrieve information of employees whose names lies with ‘r’ or salary less than 3000.

Select * from employee where name like ‘r%’ or sal<3000;

 

• Not operator:

The oracle engine will process all rows in a table and display only those records that do not satisfy the condition specified.

E.g: retrieve all information of clients whose salary is not equal to 12000.

In the above query, we can easily write different methods of not operator.

1. Select * from client where sal != 12000;
2. Select * from client where not sal = 12000.
3. Select * from client where sal<> 12000;