SQL

SQL: Structured Query Language.

It is a language that provides an interface to relational database systems.

SQL was developed by IBM in the 1970s for use in System R.

SQL is a de facto standard, as well as an ISO and ANSI standard.

It is a special-purpose, nonprocedural language that supports the definition, manipulation, and control of data in relational database management systems.

SQL Introduction Introduction to SQL SQL stands for Structured Query Language and is used to access and manipulate information from database.     SQL   SQL allows users to access data in relational database management systems, such as Oracle,Informix,Sybase, Microsoft SQL Server, Access etc.   SQL allows users to manipulate and define the data in a database.   SQL is an ANSI standard computer language   Strucured Query Language   SQL may frequently pronounced “sequel” but the alternate pronunciation “S.Q.L.” is also used. As the name implies, SQL is a computer language that you use to interact with a database. In fact, SQL works with a specific type of database, called a relational database. SQL is more than a query tool, although that was its original purpose, and retrieving data is still one of its most important functions. SQL is used to control all of the functions of DBMS which provides for its users, including: Data Definition Language. In DDL user define the structure and organization of the stored data and relationships among the stored data items.   Data Retrieval Language. An application program to retrieve stored data from the database and use it.   Data Manipulation Language. DML allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.   Data Control Language. DCL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.   Data sharing. Is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.   Data intigrity.It defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.   SQL Queries   Query is a loose term that refers to a widely available set of SQL commands called clauses. Each clause or command performs some sort of function against the database. For instance, the create clause creates tables in databse and the select clause selects rows that have been inserted into your tables. Construction of Query begins with one of the following clauses: Add Drop Create Insert Select Update Replace Delete SQL Query Syntax The syntax of a query is loose, meaning you are free to place line breaks and spacing where you please without injuring the code.Few instances require parentheses, including the insert statement listed below. Parentheses will also be covered during our Functions lesson. Be sure to end all query statements with a semicolon (;). SQL Code:   SELECT * FROM table_name;

SQL Select Statement Select command is use to select data from the tables   Select Command Select command is use to select data from the tables located in a database.If we need to SELECT information FROM a table. The most basic SQL structure: A D V E R T I S E M E N T   SELECT "column_name" FROM "table_name"   To illustrate the above example, assume that we have the following table Name "Store_Information" :   store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   To select all the stores in this table   SELECT store_name FROM Store_Information   Output   Store_name Los Angeles San Diego Los Angeles Boston   Distinct Keyword: The SELECT keyword allows us to take all information from a column (or columns) on a table. Of course, there is probability of redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:   SELECT DISTINCT "column_name" FROM "table_name"   For example, to select all distinct stores in Table "Store_Information".   SELECT DISTINCT store_name FROM Store_Information   Output   Store_name Los Angeles San Diego Boston

SQL WHERE Clause To conditionally select the data from a table The WHERE Clause A D V E R T I S E M E N T     WHERE Syntax To conditionally select the data from a table the WHERE clause is used . eg.if we want to 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.   store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005 SQL command SELECT store_name FROM Store_Information WHERE Sales > 1000   Notes: SQL uses single quotes around string values (most database systems will also accept double quotes). Numeric values not be enclosed in quotes. Output store_name Los Angeles     The following operators can be used with where clause:   Operator Description = Equal <>  Not equal >  Greater than <  Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern   Note: In some versions of SQL the <> operator is equivalent to != operator

SQL Insert Statement The insert clause has one function; to insert data into a table. Insert Command The insert clause has one function; to insert data into a table. Insert populates each table column with a value. Rows are inserted one right after another into the coresponding column. A D V E R T I S E M E N T     SQL Code:   INSERT INTO employees (Lastname,Firstname,Title) VALUES(Johnson,David,crew);   Output Lastname Firstname Title Johnson David crew     SQL - Insert defaults and nulls   We mentioned setting up default or null values for table columns. Simply placing the word default or null, in place of a value is the solution. SQL Code: INSERT INTO employees (Lastname,Firstname,Title) VALUES('Hively','Jessica',DEFAULT); or INSERT INTO employees (Lastname,Firstname,Title) VALUES('Hively','Jessica',NULL);     SQL - Inserting multilpe values   Here's an example of how to insert more than one record at a time. Many web developers will use the single example above along with HTML forms to continually insert and update their SQL tables. SQL Code: INSERT INTO employees VALUES (DEFAULT,'Hicks','Freddy','crew'), (DEFAULT,'Harris','Joel','crew'), (DEFAULT,'Davis','Julie','manager');     SQL - Insert into multiple tables   This concept isn't widely supported by open source database programs, however they do offer alternative methods to achieve the same goal. The idea is to insert similar record values into 2 or more tables with one statement. Using the example from above, we want to place Julie's information into our manager table as well as the general employee table. SQL Code: INSERT ALL INTO employees (Lastname,Firstname,Title) VALUES('Davis','Julie','manager') INTO manager (training,salary) VALUES ('yadayada','22500');

SQL Update Statement This command updates the column values of a table. Update Command   This command updates the column values of a table. >Update requires a conditional statement for selecting the row which is to be updated

SQL Delete Statement Delete command removes Entire rows from a table. The order by statement allows for table column range. It allows for ascending or descending lists of column values, permitting SQL to reorder your table rows for the purpose of viewing.   Syntax: SELECT * FROM employees ORDER BY Lastname;   Output:- Lastname Firstname Title Davis Julie manager Harris Joel crew Hicks Freddy crew Hively Jessica crew Johnson David crew The above example arrange our rows of data alphabetically by lastname.   Ordering by two different columns First we alphabetize our job titles and again we order by lastname. Syntax: SELECT * FROM employees ORDER BY Title,Lastname;   Output:- Lastname Firstname Title Harris Joel crew Hicks Freddy crew Hively Jessica crew Johnson David crew Davis Julie manager

SQL Order By Clause Order By Command The order by statement allows for table column range. WHERE keyword might be used to conditionally select data from a table.

SQL AND OR Operator WHERE keyword might be used to conditionally select data from a table. WHERE keyword might 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. There is no limit to the number of conditions that can be present in a single SQL statement.   Syntax of Compound condition SELECT "column_name" FROM "table_name" WHERE "simple condition" {[AND|OR] "simple condition"}+ Note:-The {}+ means that the expression inside the bracket will occur one or many times.AND and OR can be used interchangeably. In addition,to indicate the order of the condition we may use the parenthesis sign (). e.g. we may wish to select all store_name with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information,   Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   For selecting the store_name following Query:   SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275)   Output   store_name Los Angeles San Francisco

SQL in operator A special kind of operator which uses with where clauses. In is a special kind of operator which uses with where clauses. In the where expression only one value is allowed to be sent through the query. With help of in operator you can send multiple values in the where clause.   The syntax of IN keyword is as follows:   Syntax SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)   Let's you wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,   The Values inside parenthesis can be one or more, with each values separated by comma. Values can be characters or numerical. If there is only one value inside the parenthesis, then command is equivalent to :-   WHERE "column_name" = 'value1'   Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Command   SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego') Output Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005

SQL Between The BETWEEN keyword allows for selecting a values in range. The BETWEEN keyword allows for selecting a values in range. The syntax for the BETWEEN clause is given below     Syntax: SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'     It select all rows whose column has a value between 'value1' and 'value2'. For example,to select all sales information between January 6, 2005, and January 10, 2005, in Table Store_Information   Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Syntax SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-2005' AND 'Jan-10-2005'   Note:Date may be stored in different formats in different databases. Output   Store_name Sales Date San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005

SQL Aliases Aliases for table and column. SQL Alias Most frequently two types of aliases are used:- Column Alias Table Alias. A D V E R T I S E M E N T   Column Name Alias Syntax:-   SELECT column AS column_alias FROM table     Table Name Alias Syntax :-   SELECT column FROM table AS table_alias     Example:   Column aliases exist to organizing output.In the previous example, whenever there is total sales, it is listed as SUM(sales). While this is understandable, we can have many cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias its make the output much more readable. Second is the table alias.This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables ('perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins. Both types of aliases are placed directly after the item they alias for, separate by a white space. We again use our table, Store_Information,   Syntax SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name   Output Store Total Sales Los Angeles $1800 San Diego $250 Boston $700   Notice that difference in the result that the column titles are now different. That is the result of using the column alias. Notice that instead of the somewhat cryptic "Sum(Sales)", we now have "Total Sales", which is much more understandable, as the column header. The advantage of using a table alias is not visible in this example. However, they will become evident in the Join Section

SQL Join Combines columns of one table to that of another. Previously, we had looked at inner join, where we select rows common to the participating tables to a join. One case is that if we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command.   Example: The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows. Let's we have the following two tables,   Table:-Store_Information Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Table:-Geography Region_name Store_name East Boston East New York West Los Angeles West San Diego   If 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".   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   Output:- Store_name SALES Boston $700 New York   Los Angeles $1800 San Diego $250   Note: When there is no match on the second table NULL is returned . In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.

SQL Outer Join A type of join. SQL Union   SQL UNION combine the results of two queries together. A D V E R T I S E M E N T In this respect, UNION is similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type and UNION only select distinct values.   Syntax:   [SQL Statement 1] UNION [SQL Statement 2]   Table Store_Information Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Table Internet_Sales Date Sales Jan-07-2005 $250 Jan-10-2005 $535 Jan-11-2005 $320 Jan-12-2005 $750   If we want to find out all the dates where there is a sales transaction.We use the following SQL statement:   SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales   Output:   Date Jan-05-2005 Jan-07-2005 Jan-08-2005 Jan-10-2005 Jan-11-2005 Jan-12-2005   SQL UNION ALL The SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, UNION ALL selects all values while UNION only selects distinct values.   Syntax :   SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales   Output :   Date Jan-05-2005 Jan-07-2005 Jan-08-2005 Jan-08-2005 Jan-07-2005 Jan-10-2005 Jan-11-2005 Jan-12-2005

SQL Union Combine the results of two queries together.

SQL Subqueries Queries placed within an existing SQL statement. SQL Subqueries Subqueries are queries placed within an existing SQL statement. A D V E R T I S E M E N T MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them. They may exist in any of the following types of SQL statements.   Syntax: SELECT "column_name1" FROM "table_name1" WHERE "column_name2" [Comparison Operator] (SELECT "column_name3" FROM "table_name2" WHERE [Condition])     Example:   Table: Store_Information Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Table: Internet_Sales Date Sales Jan-07-2005 $250 Jan-10-2005 $535 Jan-11-2005 $320 Jan-12-2005 $750   If we want to use a subquery to find the sales of all stores in the West region. Then we use the following SQL statement:   SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN (SELECT store_name FROM Geography WHERE region_name = 'West')   Output:   SUM(Sales) 2050   Here instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we use subquery to find out which stores are in the West region and then add the sales amount for these stores.

SQL Date Function All SQL platform has its own version of date functions. SQL Dates All SQL platform has its own version of date functions. A D V E R T I S E M E N T     SQL - Timestamp A timestamp servers as the catch all for dates and times. Retrieving a timestamp is very simple and the result can be manipulated in nearly every way possible. Syntax:   Select Current_Timestamp;   Return a Timestamp:   2005-06-22 11:33:11.840   SQL Date Functions By using any of the following date functions it is possible to breakdown timestamps into their individual pieces. SQL Function Code:   SELECT MONTH(CURRENT_TIMESTAMP);- Return a Month(6) SELECT DAY(CURRENT_TIMESTAMP);-Return a Day:(22) SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22) SELECT TIME(CURRENT_TIMESTAMP); - returns the time (10:33:11.840) SELECT DAYOFWEEK(CURRENT_TIMESTAMP); - returns a numeric value (1-7) SELECT DAYOFMONTH(CURRENT_TIMESTAMP); - returns a day of month (1-31) SELECT DAYOFYEAR(CURRENT_TIMESTAMP); - returns the day of the year (1-365) SELECT MONTHNAME(CURRENT_TIMESTAMP); - returns the month name (January - December) SELECT DAYNAME(CURRENT_TIMESTAMP); - returns the name of the day (Sunday - Saturday) SELECT WEEK(CURRENT_TIMESTAMP); - returns number of the week (1-53)

SQL Create Statement Create statement in SQL Create Table Statement   Tables are the structure where data is stored in the database. In most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, then tables in the database makes itself. Many database tools allow you to create tables automatically,but always tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial. A D V E R T I S E M E N T Tables are divided into rows and columns. Each row represents one piece of data, and each column represent a component of piece of data.Example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, birth Date, Address, City, Country, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column. Data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a date/time expression (such as '2000-JAN-25 03:22:22'),a string (such as 'sql'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(40) - meaning it is a string with 40 characters). One thing to note is that different relational databases allow for different data types.   Syntax   CREATE TABLE "table_name" ("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2", ... )   Example   CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date)     Create Index Statement Index are used to retrieve data from tables by quicker way. Let's use an example it is much quicker for us to go to the index section at the end of the book, locate which pages contain information that we want and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. This principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster. Therefore, it is often desirable to create indexes on tables. An index can cover one or more columns. Syntax CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)   Example CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)     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 consist of one or more fields on a table. When multiple fields are used as a primary key then it is called composite key. Primary keys can be specified either when the table is created or by changing the existing table structure with alter command.   CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));   ALTER TABLE Customer ADD PRIMARY KEY (SID);     Foreign Key A foreign key is a field that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity means 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. By 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. Both Table is given below:-   Table CUSTOMERS 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. Syntax   CREATE TABLE ORDERS (Order_ID integer, Order_Date date, Customer_SID integer, Amount double, Primary Key (Order_ID), Foreign Key (Customer_SID) references CUSTOMER(SID));

SQL Drop Statement Delete an existing index in a table with the DROP INDEX statement. Drop Index You can delete an existing index in a table with the DROP INDEX statement. Syntax: Microsoft Access   DROP INDEX index_name ON table_name   Syntax: MS SQL Server   DROP INDEX table_name.index_name   Syntax: IBM DB2 and Oracle   DROP INDEX index_name   Syntax: MySQL   ALTER TABLE table_name DROP INDEX index_name     Delete a Table or Database To delete a table (the table structure, attributes, and indexes will also be deleted):   DROP TABLE table_name   To delete a database:   DROP DATABASE database_name

SQL Alter Change the structure of the table. The order by statement allows for table column range. It allows for ascending or descending lists of column values, permitting SQL to reorder your table rows for the purpose of viewing.   Syntax: SELECT * FROM employees ORDER BY Lastname;   Output:- Lastname Firstname Title Davis Julie manager Harris Joel crew Hicks Freddy crew Hively Jessica crew Johnson David crew The above example arrange our rows of data alphabetically by lastname.   Ordering by two different columns First we alphabatize our job titles and again we order by lastname. Syntax: SELECT * FROM employees ORDER BY Title,Lastname;   Output:- Lastname Firstname Title Harris Joel crew Hicks Freddy crew Hively Jessica crew Johnson David crew Davis Julie manager

SQL Function A function is a special type of command that return a single value. Types of Functions The basic types of functions are:- Aggregate functions It operate against a collection of values, but return a single Value.   Scalar functions It operate against a single value, and return a single value based on the input value.Functions like CURRENT_TIME for example, do not require any arguments.   Syntax:   SELECT function(column) FROM table   Aggregate Functions It return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement Supported aggregate functions are:   Function Usage AVG(expression) Computes the average value of a column by the expression COUNT(expression) Counts the rows defined by the expression COUNT(*) Counts all rows in the specified table or view MIN(expression) Finds the minimum value in a column by the expression MAX(expression) Finds the maximum value in a column by the expression SUM(expression) Computes the sum of column values by the expression   Example:   SELECT COUNT(*) FROM customers;   Scalar Functions Different categories of Scalar Function are:- Built-in Function Date & Time Function Numeric Function String Function Some Scalar Functions are:-   Function Description UCASE(c) Converts a field to upper case LCASE(c) Converts a field to lower case MID(c,start[,end]) Extract characters from a text field LEN(c) Returns the length of a text field INSTR(c,char) Returns the numeric position of a named character within a text field LEFT(c,number_of_char) Return the left part of a text field requested RIGHT(c,number_of_char) Return the right part of a text field requested ROUND(c,decimals) Rounds a numeric field to the number of decimals specified MOD(x,y) Returns the remainder of a division operation NOW() Returns the current system date FORMAT(c,format) Changes the way a field is displayed DATEDIFF(d,date1,date2) Used to perform date calculations

SQL Group By Statement The aggregate of all column values. Group By clause When Aggregate functions (like SUM) called it return the aggregate of all column values.It was impossible to find the sum for each individual group of column values so Group By clause used. A D V E R T I S E M E N T     Syntax:   SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"   Let's following table "Store_Information"   Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   To find total sales for each store:-   SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name Output Store_name SUM(Sales) Los Angeles $1800 San Diego $250 Boston $700   The GROUP BY keyword is used when selecting multiple columns from tables and at least one arithmetic operator appears in the SELECT statement.   Having Clause To limit the output based on the corresponding sum (or any other aggregate functions). 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 placed near the end of the SQL statement. SQL statement with the HAVING clause may or may not include the GROUP BY clause. Syntax: Having Clause   SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithmetic function condition)   In our example, table "Store_Information" we would type.   SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500 Output store_name SUM(Sales) Los Angeles $1800

SQL Select Into Make a backup copy To create a make-table query means for making backup copies of tables and reports, or for archiving records.     Syntax   SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source   Make a Backup Copy The example given below makes a backup copy of the "Persons" table:   SELECT * INTO [Customers Backup] FROM Customers;   The IN clause may be used to copy tables into another database:   SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb' FROM Suppliers;   If you only want to copy a few fields not copy whole Table, you can do so by listing them after the SELECT statement. The following query creates a Fiddlers table by extracting the names of fiddlers from a Musicians table:   SELECT Name INTO Fiddlers FROM Musicians WHERE Instrument = 'fiddle';   The fields which you copy into a new table need not come from just one table. You can copy from multiple tables as demonstrated in the next example which selects fields from the two tables Suppliers and Products to create a new table for Mexican Suppliers:   SELECT Suppliers.Name, Product, Products.UnitPrice INTO [Mexican Suppliers] FROM Suppliers INNER JOIN Products ON Suppliers.ProductID = Products.ProductID WHERE Suppliers.Country = 'Mexico';   The SELECT...INTO statement doesn't define a primary key for the new table, so you may want to do that manually.

SQL Create View Virtual table based on the result-set of a SELECT statement. A VIEW is a virtual table based on the result-set of a SELECT statement. A view contains rows and columns like a real table. The fields in a view consist fields from one or more real tables in the database.     Syntax:   Views may be considered as virtual tables. Generalize way 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.   CREATE VIEW "VIEW_NAME" AS "SQL Statement"   Example:   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))     Apply View on Joins To joins two or more tables view can be used. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:   Table:Store_Information   Store_name Sales Date Los Angeles $1500 Jan-05-2005 San Diego $250 Jan-07-2005 Los Angeles $300 Jan-08-2005 Boston $700 Jan-08-2005   Table: Geography   region_name store_name East Boston East New York West Los Angeles West San Diego   If we want to build a view that has sales by region information. We would issue the following SQL statement:   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   It gives a view called V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view then following query:   SELECT * FROM V_REGION_SALES   Output:   REGION SALES East $700 West $2050

SQL Server SQL server SQL Servers - RDBMS Modern SQL Servers used Relational Data Base Management System.     DBMS - Database Management System A DBMS is a computer program that can access data from a database. The DBMS program enables you to modify, store and extract information from a database. DBMS programs provides different functions for querying data, modifying data and reporting data.     RDBMS - Relational Database Management System A RDBMS is a Database Management System where the database is organized and accessed according to the relationships between data stored in table. it was invented by IBM in the early 1970's. RDBMS is the base of SQL for all modern database systems like Oracle, SQL Server, IBM DB2, Mesql, Sybase and Microsoft Access.   SELECT * FROM V_REGION_SALES

SQL Summary Summary SQL Servers - RDBMS Modern SQL Servers used Relational Data Base Management System.     DBMS - Database Management System A DBMS is a computer program that can access data from a database. The DBMS program enables you to modify, store and extract information from a database. DBMS programs provides different functions for querying data, modifying data and reporting data.     RDBMS - Relational Database Management System A RDBMS is a Database Management System where the database is organized and accessed according to the relationships between data stored in table. it was invented by IBM in the early 1970's. RDBMS is the base of SQL for all modern database systems like Oracle, SQL Server, IBM DB2, Mesql, Sybase and Microsoft Access.   SELECT * FROM V_REGION_SALES