SQL Commands

SQL PRIMARY KEY Constraint :-
--------------------------
The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns.


CREATE TABLE EMP (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR2(20), 
SAL INT
);


SQL FOREIGN KEY Constraint :-
--------------------------
A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
CREATE TABLE DEPT (
DEPTNO INT,
DNAME VARCHAR2(20),
LOC VARCHAR2(20),
EMPNO INT,
CONSTRAINT FK_DEPT FOREIGN KEY (EMPNO) REFERENCES EMP (EMPNO));




SQL CHECK Constraint :-
---------------------
The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.


CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);



SQL DEFAULT Constraint :-
-----------------------
The DEFAULT constraint is used to provide a default value for a column.

The default value will be added to all new records IF no other value is specified.


CREATE TABLE Person (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);



SQL AUTO INCREMENT Field :-
-------------------------
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.


CREATE TABLE Personal (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
 
The SQL SELECT Statement :-
-------------------------
The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

(Select * from employee;)



The SQL SELECT DISTINCT Statement :-
----------------------------------
The SELECT DISTINCT statement is used to return only distinct (unique) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the distinct (unique) values.

(select distinct salary from employee;)



The SQL WHERE Clause :-
---------------------
The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

(select * from emp where sal <= 4000;)



SQL AND, OR, IN and NOT Operators :-
------------------------------
The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND is TRUE.

The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

(select * from emp where rownum <=5 and sal <=5000;)
(select * from emp where rownum <=5 or sal <=5000;
(select * from emp where job in ('president','manager','clerk';)
(select * from emp where job not in ('president','manager','clerk';)



The SQL ORDER BY Keyword :-
-------------------------
The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

(select * from emp order by sal)
(select * from emp order by sal desc)



The SQL INSERT INTO Statement :-
--------------------------------------
The INSERT INTO statement is used to insert new records in a table.

(insert into emp (empno,ename,sal,deptno)
values (1233,'alex',3000,10);)
               or
(insert into emp values (1234,'aaa','clerk',123,'12-jan-98',1200,100,10); 

The SQL UPDATE Statement :-
-------------------------
The UPDATE statement is used to modify the existing records in a table.


(update emp set ename='Alex' where ename ='aaa';)


To update Multiple Columns in Oracle :

UPDATE table SET column1 = value1, column2 = value2, columnX = valueX
WHERE <conditions>;
                            OR
UPDATE departments dep
SET dep.department_name = 'Customer Relations',
dep.manager_id = 205
WHERE dep.department_id = 20;



The SQL DELETE Statement :-
-------------------------
The DELETE statement is used to delete existing records in a table.

(delete from emp where empno=1234;)



SQL ALTER TABLE Statement :-
--------------------------------

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Alter table emp1 add zipcode varchar2(20);
Alter table emp1 drop column zipcode;
Alter table emp1 modify zipcode varchar2(20);




The SQL NULL Values :-
---------------------
A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.


SQL TOP, LIMIT or ROWNUM Clause :-
---------------------------------
The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact on performance

(select * from emp where rownum <=5;)



The SQL MIN() and MAX() Functions :-
-----------------------------------
The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

(select min(sal) from emp;)
(select max(sal) from emp;)



The SQL COUNT(), AVG() and SUM() Functions :-
-------------------------------------------
The COUNT() function returns the number of rows that matches a specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column

(select avg(sal) from emp;)
(select sum(sal) from emp;)
(select count(empno) from emp;)



The SQL LIKE Operator -
---------------------
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

(select * from emp where sal like '%0';)
(select * from emp where sal like '0%';)
(select * from emp where sal like '2_%';)



The SQL BETWEEN Operator :-
-------------------------
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

(select * from emp where sal between 2000 and 3000;)



SQL Aliases :-
------------
SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of the query.

(select e1.empno, e2.ename, e1.sal, e2.deptno from emp e1, emp e2 where e1.empno=e2.empno;)



The SQL UNION Operator :-
------------------------
The UNION operator is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within UNION must have the same number of columns

The columns must also have similar data types

The columns in each SELECT statement must also be in the same order

(select * from emp union select * from emp1;)



The SQL UNION ALL Operator :-
-----------------------------------
Only difference between union and union all is union all will return duplicates.

Where as union will not allow duplicates.


(select * from emp union all select * from emp1;)




The SQL GROUP BY Statement :-
----------------------------
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

(select sum(sal) from emp group by sal;)




The SQL HAVING Clause :-
-----------------------
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.



HAVING clause should be used when you use both Aggregate functions as well as Group by.


(select sum(sal) from emp group by sal having sal <= 5000;)




The SQL EXISTS Operator :-
-------------------------
The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns true if the subquery returns one or more records.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);



COMMAND TO CREATE USER :-
---------------------------------
CREATE USER IDENTIFIED BY USER;


COMMAND TO GRANT USER PERMISSION :-
-----------------------------------------------
GRANT DBA TO USER;