- INTRODUCTION TO SQL
Table Basics
SQL Syntax
SQL SELECT COMMAND
Creating Tables
Inserting into a Table
Updating Records
Deleting Records
Drop a Table
SQL DISTINCT
SQL WHERE
SQL AND OR
SQL IN
SQL BETWEEN
SQL LIKE
SQL ORDER BY
SQL Functions
SQL COUNT
SQL GROUP BY
SQL HAVING
SQL JOIN
SQL OUTER JOIN
SQL CONCATENATE
SQL TRIM
SQL CONSTRAINT
PRIMARY KEY
FOREIGN KEY IN SQL
SQL CREATE VIEW
Truncate Table Statement
TABLE OF CONTENTS
| ...... ATM NETWORK ......... | ... COMPUTER NETWORK.. |
| DATA COMMUNICATION | ..... NETWORK MANAGEMENT |
| ..NETWORK SECURITY | |
.. ISDN |
SQL/DBMS |
| MORE TO COME... |
INDEX OF SQL/DBMS
Labels: DBMS/SQL
Truncate Table Statement
The syntax for "TRUNCATE TABLE" is:-
TRUNCATE TABLE "table_name"
For example, if we wanted to truncate the table called customer then in SQL, we simply type the following table:-
TRUNCATE TABLE customer
This query get rid all of the data but not table itself.
Labels: DBMS/SQL
SQL CREATE VIEW
Views can be considered as virtual tables. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.
The syntax for creating a view is as follows:
CREATE VIEW "VIEW_NAME" AS "SQL Statement"
Let's use a simple example to illustrate. Say we have the following table:
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
Now we have a view called V_Customer with the following structure:
View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
This gives us a view, V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view, we type in,
SELECT * FROM V_REGION_SALES
Result:
REGION SALES
East $700 Read More......
Labels: DBMS/SQL
FOREIGN KEY IN SQL
For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.
The structure of these two tables will be as follows:
Table CUSTOMER
| column name | characteristic |
| SID | Primary Key |
| Last_Name | |
| First_Name |
| column name | characteristic |
| Order_ID | Primary Key |
| Order_Date | |
| Customer_SID | Foreign Key |
| Amount |
In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);
Labels: DBMS/SQL
PRIMARY KEY
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
Below are examples for specifying a primary key when creating a table:
SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Labels: DBMS/SQL
SQL CONSTRAINT
Common types of constraints include the following:
- NOT NULL
- UNIQUE
- CHECK
- Primary Key
- Foreign Key
NOT NULL
By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.
For example, in the following statement,
CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));
Columns "SID" and "Last_Name" cannot include NULL, while "First_Name" can include NULL.
UNIQUE
The UNIQUE constraint ensures that all values in a column are distinct.
For example, in the following statement,
CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));
Column "SID" cannot include duplicate values, while such constraint does not hold for columns "Last_Name" and "First_Name".
Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key.
CHECK
The CHECK constraint ensures that all values in a column satisfy certain conditions.
For example, in the following statement,
CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
Column "SID" must only include integers greater than 0.
Labels: DBMS/SQL
SQL TRIM
The syntax for these trim functions are:
TRIM([[LOCATION] [remstr] FROM ] str): [LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.
LTRIM(str): Removes all white spaces from the beginning of the string.
RTRIM(str): Removes all white spaces at the end of the string.
Example 1:
SELECT TRIM(' Sample ');
Result:
'Sample'
Example 2:
SELECT LTRIM(' Sample ');
Result:
'Sample '
Example 3:
SELECT RTRIM(' Sample ');
Result:
' Sample'
Labels: DBMS/SQL
SQL CONCATENATE
CONCAT(str1, str2, str3, ...): Concatenate str1, str2, str3, and any other strings together. Please note the Oracle CONCAT() function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.
Let's look at some examples. Assume we have the following table:
Table Geography
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
Example 1:
MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';
Result:
'EastBoston'
Example 2:
Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';
Result:
'East Boston'
Example 3:
SQL Server:
SELECT region_name + ' ' + store_name FROM Geography
WHERE store_name = 'Boston';
Result:
'East Boston'
Labels: DBMS/SQL
SQL OUTER JOIN
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Note that in this case, we are using the Oracle syntax for outer join.
Result:
store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
Note: NULL is returned when there is no match on the second table. In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.
Labels: DBMS/SQL
SQL JOIN
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
Table Geography
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
and we want to find out sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables. Examining the two tables, we find that they are linked via the common field, "store_name". We will first present the SQL statement and explain the use of each segment later:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Result:
REGION SALES
East $700
West $2050
Labels: DBMS/SQL
SQL HAVING
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)
Note: the GROUP BY clause is optional.
In our example, table Store_Information.
Table Store_Information
| store_name | Sales | 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 store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
Results:-
| Los Angeles | $1800 |
Labels: DBMS/SQL
SQL GROUP BY
Previously,we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores. The corresponding SQL syntax is,
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
Let's illustrate using the following table.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
We want to find total sales for each store. To do so, we would key in,
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
Result:
Los Angeles $1800
San Diego $250
Boston> $700
The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. Read More......
Labels: DBMS/SQL
SQL COUNT
Another arithmetic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is,
SELECT COUNT("column_name")
FROM "table_name"
For example, if we want to find the number of store entries in our table.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we'd key in
SELECT COUNT(store_name)
FROM Store_Information
COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. For example, if we want to find out the number of distinct stores, we'd type,
SELECT COUNT(DISTINCT store_name)
FROM Store_Information Read More......
Labels: DBMS/SQL
SQL Functions
SQL has several arithematic functions, and they are:
- AVG
- COUNT
- MAX
- MIN
- SUM
The syntax for using functions is,
SELECT "function type"("column_name")
FROM "table_name"
For example, if we want to get the sum of all sales from the following table.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we would type in
SELECT SUM(Sales) FROM Store_Information. Read More......
Labels: DBMS/SQL
SQL ORDER BY
The syntax for an ORDER BY statement is as follows:
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]
The [] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.
It is possible to order by more than one column. In this case, the ORDER BY clause above becomes
ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]
Assuming that we choose ascending order for both columns, the output will be ordered in ascending order according to column 1. If there is a tie for the value of column 1, we the sort in ascending order by column 2.
For example, we may wish to list the contents of Table Store_Information by dollar amount, in descending order:
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| San Francisco | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we key in,
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
Result:
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| Boston | $700 | Jan-08-1999 |
| San Francisco | $300 | Jan-08-1999 |
| San Diego | $250 | Jan-07-1999 |
Labels: DBMS/SQL
SQL LIKE
Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN). The syntax for is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
{PATTERN} often consists of wildcards. Here are some examples:
* 'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one).
* 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition.
* '%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would both satisfy the condition.
* '%AN%': All string that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.
Table Store_Information
| store_name | Sales | Date |
| LOS ANGELES | $1500 | Jan-05-1999 |
| SAN DIEGO | $250 | Jan-07-1999 |
| SAN FRANCISCO | $300 | Jan-08-1999 |
| BOSTON | $700 | Jan-08-1999 |
We want to find all stores whose name contains 'AN'. To do so, we key in,
SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%'
Result:
| store_name | Sales | Date |
| LOS ANGELES | $1500 | Jan-05-1999 |
| SAN DIEGO | $250 | Jan-07-1999 |
| SAN FRANCISCO | $300 | Jan-08-1999 |
Labels: DBMS/SQL
SQL BETWEEN
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
This will select all rows whose column has a value between 'value1' and 'value2'.
For example, we may wish to select view all sales information between January 6, 1999, and January 10, 1999, in Table Store_Information.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| San Francisco | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we key in,
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'
Result:
| store_name | Sales | Date |
| San Diego | $250 | Jan-07-1999 |
| San Francisco | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
Labels: DBMS/SQL
SQL IN
In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the WHERE clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns. The syntax for using the IN keyword is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)
The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters. If there is only one value inside the parenthesis, this commend is equivalent to
WHERE "column_name" = 'value1'
For example, we may wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| San Francisco | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we key in,
SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles', 'San Diego')
Result:
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
Labels: DBMS/SQL
SQL AND OR
The syntax for a compound condition is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
The {}+ means that the expression inside the bracket will occur one or more times. Note that AND and OR can be used interchangeably. In addition, we may use the parenthesis sign () to indicate the order of the condition.
For example, we may wish to select all stores with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information:-
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| San Francisco | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we key in,
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales <> 275)
Result:
store_name
Los Angeles
San Francisco
Labels: DBMS/SQL
SQL WHERE
we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information.
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
we key in,
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
Result:
store_name
Los Angeles Read More......
Labels: DBMS/SQL
disable right
Labels
- ATM NETWORK (13)
- C (44)
- COMPUTER NETWORK (38)
- DATA COMMUNICATION (24)
- DBMS/SQL (30)
- DOWNLOAD E BOOKS (1)
- HTML (1)
- ISDN (4)
- NETWORK MANAGEMENT (27)
- NETWORK SECURITY (11)
- UML (6)
- WEB ENGG. (17)
About Me
- prince kumar
- bhopal, M.P., India