Knowledge in Sql tags
SQL SELECT DISTINCT Statement
The SQL SELECT DISTINCT StatementThe SELECT DISTINCT statement is used to return only distinct (different) values.Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.SELECT DISTINCT Syntax SELECT DISTINCT column1, column2, ... FROM table_name; SELECT Example Without DISTINCTThe following SQL statement selects ALL (including the duplicates) values from the "Country" column in the "Customers" table:Example SELECT Country FROM Customers;
Jay Kakadiya
SELECT DISTINCT Examples
SELECT DISTINCT ExamplesThe following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:Example SELECT DISTINCT Country FROM Customers; The following SQL statement lists the number of different (distinct) customer countries:Example SELECT COUNT(DISTINCT Country) FROM Customers;
SQL WHERE Clause
The SQL WHERE ClauseThe WHERE clause is used to filter records.The WHERE clause is used to extract only those records that fulfill a specified condition.WHERE Syntax SELECT column1, column2, ... FROM table_name WHERE condition; WHERE Clause ExampleThe following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:Example SELECT * FROM Customers WHERE Country='Mexico';
SQL AND, OR and NOT Operators
SQL AND, OR and NOT OperatorsThe WHERE clause can be combined with AND, OR, 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 are 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.AND Syntax SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; OR Syntax SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...; NOT Syntax SELECT column1, column2, ... FROM table_name WHERE NOT condition;
Example
AND ExampleThe following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":Example SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; OR ExampleThe following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":Example SELECT * FROM Customers WHERE City='Berlin' OR City='München'; The following SQL statement selects all fields from "Customers" where country is "Germany" OR "Spain":Example SELECT * FROM Customers WHERE Country='Germany' OR Country='Spain'; NOT ExampleThe following SQL statement selects all fields from "Customers" where country is NOT "Germany":Example SELECT * FROM Customers WHERE NOT Country='Germany'; Combining AND, OR and NOTYou can also combine the AND, OR and NOT operators.The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):Example SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":Example SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
SQL ORDER BY Keyword
The SQL ORDER BY KeywordThe 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.ORDER BY Syntax SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
SQL INSERT INTO Statement
The SQL INSERT INTO StatementThe INSERT INTO statement is used to insert new records in a table.INSERT INTO SyntaxIt is possible to write the INSERT INTO statement in two ways.The first way specifies both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
SQL NULL Values
What is a NULL Value?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.Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!How to Test for NULL Values?It is not possible to test for NULL values with comparison operators, such as =, <, or <>.We will have to use the IS NULL and IS NOT NULL operators instead.IS NULL Syntax SELECT column_namesFROM table_name WHERE column_name IS NULL; IS NOT NULL Syntax SELECT column_namesFROM table_name WHERE column_name IS NOT NULL;
SQL UPDATE Statement
The SQL UPDATE StatementThe UPDATE statement is used to modify the existing records in a table.UPDATE Syntax UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!UPDATE TableThe following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.Example UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
SQL DELETE Statement
The SQL DELETE StatementThe DELETE statement is used to delete existing records in a table.DELETE Syntax DELETE FROM table_name WHERE condition;SQL DELETE ExampleThe following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:Example DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';Delete All RecordsIt is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name; The following SQL statement deletes all rows in the "Customers" table, without deleting the table:Example DELETE FROM Customers;
SQL TOP, LIMIT or ROWNUM Clause
The SQL SELECT TOP ClauseThe 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 performance.SQL Server / MS Access Syntax: SELECT TOP number|percent column_name(s) FROM table_nameWHERE condition; MySQL Syntax: SELECT column_name(s) FROM table_nameWHERE condition LIMIT number; Oracle Syntax: SELECT column_name(s) FROM table_name WHERE ROWNUM <= number; SQL TOP, LIMIT and ROWNUM ExamplesThe following SQL statement selects the first three records from the "Customers" table:Example SELECT TOP 3 * FROM Customers;
SQL level
SQL TOP PERCENT ExampleThe following SQL statement selects the first 50% of the records from the "Customers" table:Example SELECT TOP 50 PERCENT * FROM Customers;The following SQL statement shows the equivalent example using ROWNUM:Example SELECT * FROM CustomersWHERE ROWNUM <= 3;The following SQL statement shows the equivalent example using the LIMIT clause:Example SELECT * FROM CustomersLIMIT 3;
Neela Balar