SQL DATA TYPES:
FREQUENTLY USED DATA TYPES:
Data type Description
CHARACTER(n) Character string. Fixed-length n
VARCHAR(n) or
CHARACTER VARYING(n) Character string. Variable length. Maximum length n
BOOLEAN Stores TRUE or FALSE values
NUMERIC(p,s) Exact numerical, precision p, scale s. Example: NUMERIC(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
INTEGER Integer numerical (no decimal). Precision 10
FLOAT Approximate numerical, mantissa precision 16
1.CREATE
DESCRIPTION:
The CREATE TABLE statement is used to create a table in a database.
Tables are organized into rows and columns; and each table must have a name.
SYNTAX:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
EXAMPLE:
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
2.INSERT
DESCRIPTION:
The INSERT INTO statement is used to insert new records in a table.
SYNTAX:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
EXAMPLE:
Assuming that we have a table that has the following structure,
Table Store_Information
Column Name Data Type
Store_Name char(50)
Manager_ID integer
Sales float
Txn_Date datetime
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales, and the Manager_ID for this store is 10. We will use the following SQL script:
INSERT INTO Store_Information (Store_Name, Manager_ID, Sales, Txn_Date)
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');
Now the table will hold the following data:
Table Store_Information
Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
Please note that we can specify the column names in any order -- the order does not have to be the same as that of the table. For example, the SQL statement before is equivalent to the SQL statement above:
INSERT INTO Store_Information (Sales, Store_Name, Manager_ID, Txn_Date)
VALUES (900, 'Los Angeles', 10, 'Jan-10-1999');
3.DELETE
DESCRIPTION:
The DELETE statement is used to delete records in a table.
SYNTAX:
DELETE FROM "table_name"
WHERE "condition";
EXAMPLE:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
and we decide not to keep any information on Los Angeles in this table. To accomplish this, we type the following SQL:
DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';
Now the table becomes,
Table Store_Information
Store_Name Sales Txn_Date
San Diego 250 Jan-07-1999
Boston 700 Jan-08-1999
4.UPDATE
DESCRIPTION:
The UPDATE statement is used to update records in a table.
SYNTAX:
UPDATE "table_name"
SET "column_1" = [new value]
WHERE "condition";
EXAMPLE:
For example, say we have a table as below:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
and we notice that the sales for Los Angeles on Jan-08-1999 is actually $500 instead of $300, and that particular entry needs to be updated. To do so, we use the following SQL query:
UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';
The resulting table would look like
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 500 Jan-08-1999
Boston 700 Jan-08-1999
Update Multiple Columns
It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:
UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE "condition";
For example, let's say we notice that the 'San Diego' entry has the wrong Sales and TXN_Date information. To fix it, we run the following SQL statement:
UPDATE Store_Information
SET Sales = 600, Txn_Date = 'Jan-15-1999'
WHERE Store_Name = 'San Diego';
The table now becomes,
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 600 Jan-15-1999
Los Angeles 500 Jan-08-1999
Boston 700 Jan-08-1999
IMPORTANT: When using the UPDATE statement, pay special attention to make sure that condition is specified. Otherwise, the value of all rows can be changed.
5.SELECT
DESCRIPTION:
The SELECT statement is used to select data from a database.
SYNTAX:
SELECT "column_name" FROM "table_name";
EXAMPLE:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
Select One Column:
To select a single column, we specify the column name between SELECT and FROM as follows:
SELECT Store_Name FROM Store_Information;
Result:
Store_Name
Los Angeles
San Diego
Los Angeles
Boston
Select Multiple Columns
We can use the SELECT statement to retrieve more than one column. To select Store_Name and Sales columns from Store_Information, we use the following SQL:
SELECT Store_Name, Sales FROM Store_Information;
Result:
Store_Name Sales
Los Angeles 1500
San Diego 250
Los Angeles 300
Boston 700
Select All Columns
There are two ways to select all columns from a table. The first is to list the column name of each column. The second, and the easier, way is to use the symbol *. For example, to select all columns from Store_Information, we issue the following SQL:
SELECT * FROM Store_Information;
Result:
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
6.DROP
DESCRIPTION:
Tables can easily be deleted/removed with the DROP statement.
SYNTAX:
DROP TABLE "table_name";
EXAMPLE:
If we want to drop the Customer table that we created simply type,
DROP TABLE Customer;
It is possible to drop more than one table at a time. To do that, list the names of all the tables we wish to drop separated by comma after DROP TABLE.
For example, if we want to drop the User_Details table and the Job_List table together, we can issue the following SQL statement:
DROP TABLE User_Details, Job_List;
7.COMMIT
DESCRIPTION:
Commit command is used to permanently save any transaction into database.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
SYNTAX:
commit;
EXAMPLE:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is the example which would delete records from the table having age = 25 and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
As a result, two rows from the table would be deleted and SELECT statement would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
8.ROLLBACK
DESCRIPTION:
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
SYNTAX:
ROLLBACK;
EXAMPLE:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
In the example, which would delete records from the table having age = 25 and then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
As a result, delete operation would not impact the table and SELECT statement would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
FREQUENTLY USED DATA TYPES:
Data type Description
CHARACTER(n) Character string. Fixed-length n
VARCHAR(n) or
CHARACTER VARYING(n) Character string. Variable length. Maximum length n
BOOLEAN Stores TRUE or FALSE values
NUMERIC(p,s) Exact numerical, precision p, scale s. Example: NUMERIC(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
DATE Stores year, month, and day values
TIME Stores hour, minute, and second values
INTEGER Integer numerical (no decimal). Precision 10
FLOAT Approximate numerical, mantissa precision 16
1.CREATE
DESCRIPTION:
The CREATE TABLE statement is used to create a table in a database.
Tables are organized into rows and columns; and each table must have a name.
SYNTAX:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
EXAMPLE:
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
2.INSERT
DESCRIPTION:
The INSERT INTO statement is used to insert new records in a table.
SYNTAX:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
EXAMPLE:
Assuming that we have a table that has the following structure,
Table Store_Information
Column Name Data Type
Store_Name char(50)
Manager_ID integer
Sales float
Txn_Date datetime
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales, and the Manager_ID for this store is 10. We will use the following SQL script:
INSERT INTO Store_Information (Store_Name, Manager_ID, Sales, Txn_Date)
VALUES ('Los Angeles', 10, 900, 'Jan-10-1999');
Now the table will hold the following data:
Table Store_Information
Store_Name Manager_ID Sales Txn_Date
Los Angeles 10 900 Jan-10-1999
Please note that we can specify the column names in any order -- the order does not have to be the same as that of the table. For example, the SQL statement before is equivalent to the SQL statement above:
INSERT INTO Store_Information (Sales, Store_Name, Manager_ID, Txn_Date)
VALUES (900, 'Los Angeles', 10, 'Jan-10-1999');
3.DELETE
DESCRIPTION:
The DELETE statement is used to delete records in a table.
SYNTAX:
DELETE FROM "table_name"
WHERE "condition";
EXAMPLE:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
and we decide not to keep any information on Los Angeles in this table. To accomplish this, we type the following SQL:
DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';
Now the table becomes,
Table Store_Information
Store_Name Sales Txn_Date
San Diego 250 Jan-07-1999
Boston 700 Jan-08-1999
4.UPDATE
DESCRIPTION:
The UPDATE statement is used to update records in a table.
SYNTAX:
UPDATE "table_name"
SET "column_1" = [new value]
WHERE "condition";
EXAMPLE:
For example, say we have a table as below:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
and we notice that the sales for Los Angeles on Jan-08-1999 is actually $500 instead of $300, and that particular entry needs to be updated. To do so, we use the following SQL query:
UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';
The resulting table would look like
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 500 Jan-08-1999
Boston 700 Jan-08-1999
Update Multiple Columns
It is also possible to UPDATE multiple columns at the same time. The syntax in this case would look like the following:
UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE "condition";
For example, let's say we notice that the 'San Diego' entry has the wrong Sales and TXN_Date information. To fix it, we run the following SQL statement:
UPDATE Store_Information
SET Sales = 600, Txn_Date = 'Jan-15-1999'
WHERE Store_Name = 'San Diego';
The table now becomes,
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 600 Jan-15-1999
Los Angeles 500 Jan-08-1999
Boston 700 Jan-08-1999
IMPORTANT: When using the UPDATE statement, pay special attention to make sure that condition is specified. Otherwise, the value of all rows can be changed.
5.SELECT
DESCRIPTION:
The SELECT statement is used to select data from a database.
SYNTAX:
SELECT "column_name" FROM "table_name";
EXAMPLE:
Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
Select One Column:
To select a single column, we specify the column name between SELECT and FROM as follows:
SELECT Store_Name FROM Store_Information;
Result:
Store_Name
Los Angeles
San Diego
Los Angeles
Boston
Select Multiple Columns
We can use the SELECT statement to retrieve more than one column. To select Store_Name and Sales columns from Store_Information, we use the following SQL:
SELECT Store_Name, Sales FROM Store_Information;
Result:
Store_Name Sales
Los Angeles 1500
San Diego 250
Los Angeles 300
Boston 700
Select All Columns
There are two ways to select all columns from a table. The first is to list the column name of each column. The second, and the easier, way is to use the symbol *. For example, to select all columns from Store_Information, we issue the following SQL:
SELECT * FROM Store_Information;
Result:
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999
6.DROP
DESCRIPTION:
Tables can easily be deleted/removed with the DROP statement.
SYNTAX:
DROP TABLE "table_name";
EXAMPLE:
If we want to drop the Customer table that we created simply type,
DROP TABLE Customer;
It is possible to drop more than one table at a time. To do that, list the names of all the tables we wish to drop separated by comma after DROP TABLE.
For example, if we want to drop the User_Details table and the Job_List table together, we can issue the following SQL statement:
DROP TABLE User_Details, Job_List;
7.COMMIT
DESCRIPTION:
Commit command is used to permanently save any transaction into database.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
SYNTAX:
commit;
EXAMPLE:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is the example which would delete records from the table having age = 25 and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
As a result, two rows from the table would be deleted and SELECT statement would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
8.ROLLBACK
DESCRIPTION:
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
SYNTAX:
ROLLBACK;
EXAMPLE:
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
In the example, which would delete records from the table having age = 25 and then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
As a result, delete operation would not impact the table and SELECT statement would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(POST YOUR COMMENTS WITHOUT FAIL)
No comments:
Post a Comment