Sql Basic Commands

Hi All,
    Some basic SQL queries.

1.Create a Table

  Syntax:CREATE TABLE table_name
  Eg-:CREATE TABLE Employee(id integer,name varchar(10),age integer,address varchar(10),salary integer,date Date);

2.Insert values to Table

  Syntax:INSERT INTO table_name(column name1,....column name) VALUES(value1,.....value
  Eg-:INSERT INTO Employee(id, name, age, address, salary,date) VALUES( 1,'Riyas',18,'address1',1000,'1-june-1992'), ( 2,'Muhammed',19,'address2',2000,'1-june-1993')

3.Update Table

  Syntax: UPDATE table_name Set column_name=value where column_name=some_value
  Eg-:UPDATE Employee SET name='Siddharth,address='address3' WHERE name='Riyas';

4.Selecting from Table

  Syntax:Select * from table_name
  Eg-:Select * from Employee
        It will give the complete Table with data.
  Eg:-SELECT name,age FROM Employee WHERE address = 'address3';
  Eg:-SELECT name,age FROM Employee WHERE address <> 'address3';
  Eg:-SELECT name,age FROM Employee WHERE date >= '1-july-1992';
  Eg:-SELECT name,age FROM Employee WHERE (date >= '1-june-1992') AND (date <= '15-december-1993');
  Eg:-SELECT name,age FROM Employee where date BETWEEN '1-june-1992' AND '15-december-1993';
  Eg:-SELECT name,age FROM Employee WHERE date Not BETWEEN '1-june-1992' AND '15-december-1993';
  Eg:-SELECT name,age FROM Employee WHERE address = 'abc' OR address = 'address1';
  Eg:-SELECT name,age FROM Employee WHERE address IN ('abc', 'address1', 'Redmond');
  Eg:-SELECT name,age FROM Employee WHERE address NOT IN ('abc', 'address1', 'Redmond');
  Eg:-SELECT name,age FROM Employee WHERE address LIKE '_bc';(finds all three-letter address that end with 'bc')
  Eg:-SELECT name,age FROM Employee WHERE name LIKE '%as';(finds all names whose last name ends with 'as')
  Eg:-SELECT name,age FROM Employee WHERE LastName LIKE '%as%'; (finds all names whose last name includes 'as')
  Eg:-SELECT name,age FROM Employee WHERE (name NOT LIKE 'a%') AND (name NOT LIKE 'b%');
  Eg:-SELECT name,age FROM Employee ORDER BY address;
  Eg:-SELECT name,age FROM Employee ORDER BY address DESC
  Eg:-SELECT name,age FROM Employee ORDER BY address ASC, age DESC
  Eg:-SELECT name,age FROM Employee Group BY address

5.ALTER TABLE

  Syntax:-ALTER TABLE table_name ADD column_name date_type
  Eg:-ALTER TABLE Employee ADD COLUMN DateOfBirth date;
  Eg:-ALTER TABLE Employee ADD COLUMN address varchar(30);
  Eg:-ALTER TABLE Employee DROP COLUMN DateOfBirth;
  Eg:-ALTER TABLE Employee MODIFY COLUMN age varchar(30);

6.Finding and Deleting Duplicate Records

  Eg:-SELECT id, name  from Employee where name IN (SELECT name from Employee Group by       name having (count(name) >1))
  Eg:-DELETE FROM Employee WHERE name NOT IN (SELECT MIN(name) FROM Mytable         GROUP BY name)
  Eg:-delete FROM Employee WHERE id NOT IN (SELECT * FROM (SELECT MIN(n.id) FROM   Employee n GROUP BY n.name))

7.Modifying Datetime

  Eg:-select create_date + interval '4 hour' from Employee(adding 4hr with a date time object)
  Eg:-select create_date + interval '8 second' from Employee(adding 8 seconds with a date time object)
  Eg:-select create_date + interval '8 minute' from Employee(adding 8 minute with a date time object)
  Eg:-select create_date + interval '4 day' from Employee(adding 4 days with a date time object)

8.Casting

  Eg:-select CAST(create_date + interval '4 hour' AS date) as od_date from Employee(giving date as       result)
  Eg:-select CAST(age AS float) from Employee;

9.Extracting

  Eg:-select EXTRACT(month FROM date_order) as VARCHAR(50) from Employee;
  Eg:-select EXTRACT(year FROM date_order) as VARCHAR(50) from Employee;

10.Case Statement

  Eg:-CASE WHEN address='abc' THEN 'Correct' ELSE 'wrong' END AS type from Employee;
  Eg:-CASE WHEN (code = 'BASIC') THEN amount ELSE 0 END AS BASIC;

11.Sum function

  Eg:-sum(age) AS total Age AS from Employee
  Eg:-CASE WHEN sum(age)<>0 THEN ((sum(age)+sum(age))/sum(age))*100*-1 ELSE 0 END AS   markup
  Eg:-select CASE WHEN hr_payslip.xo_total_no_of_days > 0 THEN              (hr_contract.xo_total_wage/hr_payslip.xo_total_no_of_days)
  ELSE(hr_contract.xo_total_wage/1) END AS daily_salary from Employee;

12.Concatenation

   Eg:-select CONCAT(CAST(EXTRACT(month FROM date_order) as                                              VARCHAR(50)),'/',CAST(EXTRACT(year FROM date_order)
 as VARCHAR(50))) from Employee;

13.Max function

   Eg:-select max(date) from employee;
   Eg:-select max(age) from employee;

14.to_char

   Eg:-to_char(date_from,'yyyy/mm') as period from Employee,

15.AVG

   Eg:-SELECT AVG(VacationHours)AS 'Average vacation hours',SUM(SickLeaveHours) AS 'Total    sick leave hours' FROM Employee
   WHERE JobTitle LIKE 'Vice President%';

16.CHECKSUM_AGG

   Eg:-SELECT CHECKSUM_AGG(CAST(Quantity AS int)) FROM Employee;

17.Count

   Eg:-SELECT COUNT(name) FROM Employee;

18.Grouping

   Eg:-SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS      'Grouping' FROM Employee GROUP BY SalesQuota WITH ROLLUP;

19.MIN

   Eg:-SELECT MIN(TaxRate) FROM employee;

20.Delete Command

  Syntax:-Delete from table_name;
  Eg:-DELETE from Employee;


21.Drop Command

  Syntax:-DROP TABLE table_name;
  Eg:-DROP TABLE Employee;

22.For avoiding division by zero

Eg:-(sum((l.qty * l.price_unit)) / NULLIF(sum((l.qty * u.factor)),0))

23.For null value replace in to zero

Eg:-COALESCE(l.cost_price, (0)::numeric)

                                     Joining Tables

a)Table:CUSTOMERS

b)Table:ORDERS

Now, let us join these two tables in our SELECT statement as follows:

SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS
WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

1.Left Joining

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax:

The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

LEFT JOIN table2

ON table1.common_field = table2.common_field;

Now, let us join these two tables using LEFT JOIN as follows:

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

LEFT JOIN ORDERS

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

2.Inner Joining

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table 1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax:

The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

Example:

Now, let us join these two tables using INNER JOIN as follows:

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

INNER JOIN ORDERS

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

3.Right Joining

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax:

The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

RIGHT JOIN table2

ON table1.common_field = table2.common_field;

Now, let us join these two tables using RIGHT JOIN as follows:

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS

RIGHT JOIN ORDERS

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

4.Full Joining

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Syntax:

The basic syntax of FULL JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

FULL JOIN table2

ON table1.common_field = table2.common_field;

Now, let us join these two tables using FULL JOIN as follows:

 SELECT  ID, NAME, AMOUNT, DATE  FROM CUSTOMERS

 FULL JOIN ORDERS

 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

5.Self Joining

The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax:

The basic syntax of SELF JOIN is as follows:

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_field = b.common_field;

Now, let us join this table using SELF JOIN as follows:

SELECT  a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b

WHERE a.SALARY < b.SALARY;

This would produce the following result:

6.Cartesian Product

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of CARTESIAN JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM  table1, table2 [, table3 ]

Now, let us join these two tables using CARTESIAN JOIN as follows:

SELECT  ID, NAME, AMOUNT, DATE

FROM CUSTOMERS, ORDERS;

This would produce the following result: