SQL

What are the differences between SQL and PL/SQL?

PL/SQL

SQL

SQL is a query execution or commanding language

PL/SQL is a complete programming language

SQL is data oriented language

PL/SQL is a procedural language SQL is very declarative in nature .It is used for manipulating data. It is used for creating applications

We can execute one statement at a time in SQL

We can execute block of statements in PL/SQL

Explain Operators of SQL.

SQL Arithmetic Operators

Operator

Description

Example

+ (Addition)

Adds values on either side of

the operator.

a + b will

give 30

- (Subtraction)

Subtracts right hand operand

from left hand operand.

a - b will

give -10

(Multiplication)

Multiplies values on either side of the operator.

a * b will

give 200

/ (Division)

Divides left hand operand by

right hand operand.

b / a will

give 2

% (Modulus)

Divides left hand operand by

right hand operand and

returns remainder.

b % a will

give 0

SQL Comparison Operators

Description

Example

Checks if the values of two operands are equal or not, if yes then condition becomes true.

(a = b) is not

true.

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a != b) is

true.

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a <> b) is

true.

Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.

(a > b) is not

true.

Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.

(a < b) is

true.

Checks if the value of left operand is greater than or equal to the value of right operand, if yes then

condition becomes true.

(a >= b) is

not true.

Checks if the value of left operand is less than or equal to the value of right operand, if yes then

condition becomes true.

(a <= b) is

true.

Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.

(a !< b) is

false.

Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

(a !> b) is

true.

SQL Logical Operators

SQL Logical Operators

ALL

The ALL operator is used to compare a value to all values in another value set.

AND

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

ANY

The ANY operator is used to compare a value to any applicable value in the list as per the condition.

BETWEEN

The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

EXISTS

The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.

The IN operator is used to compare a value to a list of literal values that have been specified.

LIKE

The LIKE operator is used to compare a value to similar values using wildcard operators.

NOT

The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

IS NULL

The NULL operator is used to compare a value with a NULL value.

UNIQUE

The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both of these datatypes are used for characters but varchar2 is used for character strings of variable length whereas char is used for character strings of fixed length. For example, if we specify the type as char(5) then we will not be allowed to store

string of any other length in this variable but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length, we can store a string of length 3 or 4 or 2 in this variable.

SQL Commands

SQL | CREATE: The CREATE TABLE statement is used to create a table in SQL.

CREATE TABLE table_name
(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
....
);

CREATE TABLE Students
(
ROLL_NO int(3),
NAME varchar(20),
SUBJECT varchar(20),
);

SQL | ALTER

ALTER TABLE - ADD

ALTER TABLE table_name
              ADD (Columnname_1  datatype,
              Columnname_2  datatype,
              …
              Columnname_n  datatype);
              
              
ALTER TABLE Student ADD (AGE number(3),COURSE varchar(40));

ALTER TABLE - DROP

ALTER TABLE table_name
DROP COLUMN column_name;

 ALTER TABLE Student DROP COLUMN COURSE;

ALTER TABLE- MODIFY

ALTER TABLE table_name
MODIFY column_name column_type;

ALTER TABLE Student MODIFY COURSE varchar(20);

ALTER TABLE- RENAME

ALTER TABLE table_name
RENAME TO new_table_name;

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

SQL | UPDATE

The UPDATE statement in SQL is used to update the data of an existing table in database.

UPDATE table_name SET column1 = value1, column2 = value2,... 
WHERE condition;


UPDATE Student SET NAME = 'PRATIK' WHERE Age = 20;
UPDATE Student SET NAME = 'PRATIK', ADDRESS = 'SIKKIM' WHERE ROLL_NO = 1;

Omitting WHERE clause: If we omit the WHERE clause from the update query then all of the rows will get updated.

SR.NO

ALTER Command

UPDATE Command

ALTER command is Data Definition Language (DDL).

UPDATE Command is a Data Manipulation Language (DML).

Alter command will perform the action on structure level and not on the data level.

Update command will perform on the data level.

ALTER Command is used to add, delete, modify the attributes of the relations (tables) in the database.

UPDATE Command is used to update existing records in a database.

SQL | DELETE Statement

The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause.

DELETE FROM table_name WHERE some_condition;

table_name: name of the table
some_condition: condition to choose particular record.

SQL | DROP TRUNCATE

DROP is used to delete a whole database or just a table.The DROP statement destroys the objects like an existing database, table, index, or view.

DROP object object_name

Examples:
DROP TABLE table_name;
table_name: Name of the table to be deleted.

DROP DATABASE database_name;
database_name: Name of the database to be deleted.

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).

TRUNCATE TABLE  table_name;
table_name: Name of the table to be truncated.
DATABASE name - student_data

DROP vs TRUNCATE

Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.
Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
Table or Database deletion using DROP statement cannot be rolled back, so it must be used wisely

SQL | GROUP BY

PreviousJOINS NextOS and Its Types

Last updated 2 years ago