[IT] SQL
Introduction What is SQL? SQL stands for Structured Query Language SQL lets you access and manipualate databases SQL becaome a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987 RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. Syntax SELECT * FROM Customers; SQL keywords are NOT case sensitive. Some database systems requires a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Most Important SQL Commands SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index SELECT 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 column1, column2, ... FROM table_name; SELECT DISTINCT The SELECT DINSTINCT 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 column1, column2, ... FROM table_name; WHERE The WHERE clause is used to filter records. It is used to extract only those records that fultill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition; Operators can be used in the WHERE clause: \(\begin{array}{|c|l|}\hline \text{Operator}&\text{Description}\\\hline \text{=}&\text{Equal}\\\hline \text{>}&\text{Greater than}\\\hline \text{<}&\text{Less than}\\\hline \text{>=}&\text{Greater than or equal}\\\hline \text{<=}&\text{Less than or equal}\\\hline \text{<>, !=}&\text{Not equal}\\\hline \text{BETWEEN}&\text{Between a certain range}\\\hline \text{LIKE}&\text{Search for a pattern}\\\hline \text{IN}&\text{To specify multiple possible values for a column}\\\hline \end{array}\) AND, OR, NOT THE 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 conditons separated by AND are TRUE. The OR operator displays a record if any of the conditons separated by OR is TRUE. THE NOT operator display a record if the condition(s) it NOT TRUE. SELECT column1, column2, ... FROM table_name; WHERE NOT condition1 AND condition2 OR condition3 ...; ORDER BY 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, used DESC keyword. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; INSERT INTO The INSERT INTO statement is used to insert new records in a table. Specify both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows: INSERT INTO table_name VALUES (value1, value2, value3, ...); IS NULL, IS NOT NULL What is a NULL Value? ...
