Showing posts with label DBMS/SQL. Show all posts
Showing posts with label DBMS/SQL. Show all posts

INDEX OF SQL/DBMS

·

  1. INTRODUCTION TO SQL
  2. Table Basics

  3. SQL Syntax

  4. SQL SELECT COMMAND

  5. Creating Tables

  6. Inserting into a Table

  7. Updating Records

  8. Deleting Records

  9. Drop a Table

  10. SQL DISTINCT

  11. SQL WHERE

  12. SQL AND OR

  13. SQL IN

  14. SQL BETWEEN

  15. SQL LIKE

  16. SQL ORDER BY

  17. SQL Functions

  18. SQL COUNT

  19. SQL GROUP BY

  20. SQL HAVING

  21. SQL JOIN

  22. SQL OUTER JOIN

  23. SQL CONCATENATE

  24. SQL TRIM

  25. SQL CONSTRAINT

  26. PRIMARY KEY

  27. FOREIGN KEY IN SQL

  28. SQL CREATE VIEW

  29. Truncate Table Statement

Read More......

Truncate Table Statement

·

In SQL Sometimes we want to get rid of all the data in a table then One way of doing this is with DROP TABLE. If we want to simply get rid of the data but not the table itself then For this purpose, we can use the TRUNCATE TABLE command.

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.

Read More......

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

Table Geography
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......

FOREIGN KEY IN SQL

·

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

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

Table ORDERS
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);

Read More......

PRIMARY KEY

·

A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite 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));

Read More......

SQL CONSTRAINT

·

You can place constraints to limit the type of data that can go into a table. Such constraints can be specified when the table when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement.

Common types of constraints include the following:

  1. NOT NULL
  2. UNIQUE
  3. CHECK
  4. Primary Key
  5. 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.

Read More......

SQL TRIM

·

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces.

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'

Read More......

SQL CONCATENATE

·

Sometimes it is necessary to combine together (concatenate) the results from several different fields.The syntax for CONCAT() is as follows:

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'

Read More......

SQL OUTER JOIN

·

Let's assume that we have the following two tables:-

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 the sales amount for all of the stores. If we do a regular join, we will not be able to get what we want because we will have missed "New York," since it does not appear in the Store_Information table. Therefore, we need to perform an outer join on the two tables above:

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.

Read More......

SQL JOIN

·

Let's assume that we have the following two tables:-

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

Read More......

SQL HAVING

·

For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of the SQL statement, and a SQL statement with the HAVING clause may or may not include the GROUP BY clause. The syntax for HAVING is,

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
we would type,

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

Results:-
Los Angeles $1800

Read More......

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......

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......

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......

SQL ORDER BY

·

we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.

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

Read More......

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

Read More......

SQL BETWEEN

·

Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN keyword allows for selecting a range. The syntax for the BETWEEN clause is as follows:

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


Read More......

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

Read More......

SQL AND OR

·

we have seen that the WHERE keyword can be used to conditionally select data from a table. This condition can be a simple condition or it can be a compound condition. Compound conditions are made up of multiple simple conditions connected by AND or OR. There is no limit to the number of simple conditions that can be present in a single SQL statement.

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


Read More......

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......

About Me

Blog Archive