SQL CREATE DATABASE Statement



Syllabus Focus: Unit 2 Module 1 Content 12_BSpecific Objective 12_B: construct a database;Content: Including forms; reports, queries, tables, tuples, relationship links, enforcing referential integrity, updates or deletions, use of foreign keys, use of macros, SQL, data validation and verification strategies; used to analyze data and provide multiple viewing and reporting of data.Explain how normal for relations impact databases;Including improve performance, data consistency, data integrity.Before the widespread use of computers, student information was kept on index cards in boxes or filing cabinets. If you and a small group of friends each put your personal details such as name, addresses and date of birth, on a record card, then it might not take too long to find those of you whose birthday is in April. However, if everyone in your school filled out a card, then it would take very much longer! Furthermore, changing information, such as addresses, could also take a long time. Other common “paper database” include telephone books, dictionaries, recipe cards and television guides. Nowadays, computerized databases are in widespread use, as they help people quickly to find information that they want. They also vary in size and use depending on what is required. Small databases, such as one that keeps information about a CD collection, can be run on a personal computer at home. Larger databases now play an important part in how our society works. Industrial, commercial and public organizations use databases to maintain their businesses and services. Other computerized databases include flight information systems and database systems in public libraries. Examples of how we use these large databases included:Booking holidays and airline tickets Using directory enquiries to search a database of millions of customers for a telephone number in a few secondsAccessing a police computer database, with requests from a police officers who want information about criminal suspects or stolen cars.What is a Database Management System (DBMS)?A DBMS is the term for programs that handle the storage, modification and retrieval of data, as well as controlling who has access to the information. E.g. Microsoft Access, Lotus Approach, FileMaker Pro and Corel Paradox.Functions of a DBMS:Data storage, retrieval and updates. The DBMS must allow its users to store, retrieve and update data.Backup and recovery. The DBMS should allow you to recover the most recent contents of the database in the event of system failure.Security. The DBMS must handle password allocation and checking, and allow access to the data that a user is authorized to use.Managing facilities for sharing a database. The DBMS has to ensure that no two users can access the same record at the time in order to modify it.What is a Database?A database is a part of a DBMS that is used for organizing and storing data in a useful and efficient manner on the computer.Terms Associated with a Database:Table (File or relation): Collection of records that belong together Field (Column or attribute): the smallest piece of data that can be stored.Record (row or tuple): a row of data in a table that contains information about a particular individual item or entity.Entity: anything or person that has characteristics.e.g. A ball may be big, red and soft; A car has a make , model, colour and number of doors; A student has a Christian name, surname, gender and date of birth. Ball, Car and student are examples of an entity.Attribute: Characteristic of an entity.e.g. Make, sale price, colour are all examples of attributes.Primary key: A field whose values are unique so can be used be used to access each record individually.Candidate key: A field that is considered a possibility for becoming the primary key. However, only one field must be chosen as the primary key. Candidate keys are entirely optional, so a table may contain none, one, or several of them.Alternate key: Any candidate field that was not used as the primary key.Foreign key: a field in one table, but it is a primary key in another table. (Appears in a table where it doesn’t really belong but it enables two tables to be linked.)Composite key: a primary key that is made up of two or more fields. Advantages and disadvantages of a computerized databaseAdvantagesDisadvantagesCan save enormous amounts of paper as well as filing space The computer(s) and peripherals required can cost a lot Data can easily be entered by keyboard or scannersIf the computer, or computer network, is not working, then the database cannot be usedSpeed- data can be found, calculated and sorted very quicklySecurity is very important as some people may attempt to get access to confidential information. Sometimes this may involve illegally hacking into the program or dataData can easily be changed and updatedThe database file can become corrupted or infected by a computer virus. This can lead to file not working properly. In some cases, the database may not work at all. Making a back-up copy of the database is therefore essentialData needs to be entered only once, yet can be represented in many different ways. A whole range of different queries and reports can be producedThere is often a limit to the size of a database file Data can be checked on entrySome databases can be complicated to usePasswords can be set to allow access only to those with permission to use the database Data stored about people may be incorrectThe data structure of a database can be changed, with new fields added, even after the database has been created. A paper-based system would have to be restarted from scratch Some databases require much time to be spent on staff training, which can be costlyData can be imported and exported to other programsA database file can be automatically linked to othersDatabases can be shared with other users if the computer is a part of a local or wide area network. This includes the internet.Panning and designing a database Planning a database is one of the most important steps in database management. It is critical that you plan before creating the files in which data will be stored. Each database should be set up for a specific purpose. Ask yourself the following questions when designing a database:What data do you want to store and what should it do?What questions will you ask of the data?What reports will you need to produce?How should the data be sorted and grouped?All of the information of an entity should be closely related. For example, in a videotape rental system, you might have one table for the name of tape, type of tape and its rating, one for customers which stores the customer name, address and telephone number, and another which stores data about the rentals. In this example there are three entities: the tape entity, the customer entity and the rental entity Reference Skeete, Kelvin, skeete, Kyle (2007). Information Technology for CSEC. United Kingdom: Cambridge University pressGay, Glenda, Blades, Ronald (2009). Oxford Information Technology for CSEC. Great Clarendon street, Oxford OX2 6DP: Oxford University pressCampbell, Howard, Wood, Alan (2010). Information Technology for CSEC examinations. Between towns road, Oxford, Ox4 3PP: Macmillan Publishers Limited SQL is a standard language for accessing databases.Our SQL tutorial will teach you how to use SQL to access and manipulate data in:MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.SQL is a standard language for accessing and manipulating databases.What is SQL?SQL stands for Structured Query LanguageSQL lets you access and manipulate databasesSQL is an ANSI (American National Standards Institute) standardWhat Can SQL do?SQL can execute queries against a databaseSQL can retrieve data from a databaseSQL can insert records in a databaseSQL can update records in a databaseSQL can delete records from a databaseSQL can create new databasesSQL can create new tables in a databaseSQL can create stored procedures in a databaseSQL can create views in a databaseSQL can set permissions on tables, procedures, and viewsSQL is a Standard - BUT....Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!Using SQL in Your Web SiteTo build a web site that shows some data from a database, you will need the following:An RDBMS database program (i.e. MS Access, SQL Server, MySQL)A server-side scripting language, like PHP or ASPSQLHTML / CSSRDBMSRDBMS stands for Relational Database Management System.RDBMS is the basis for SQL, and for all modern database systems like 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.Database TablesA database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.Below is an example of?a table called "Persons":P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).SQL CREATE DATABASE StatementThe CREATE DATABASE StatementThe CREATE DATABASE statement is used to create a database.SQL CREATE DATABASE SyntaxCREATE DATABASE database_nameCREATE DATABASE ExampleNow we want to create a database called "my_db".We use the following CREATE DATABASE statement:CREATE DATABASE my_dbDatabase tables can be added with the CREATE TABLE statement.SQL CREATE TABLE StatementThe CREATE TABLE StatementThe CREATE TABLE statement is used to create a table in a database.SQL CREATE TABLE SyntaxCREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....)The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.CREATE TABLE ExampleNow we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.We use the following CREATE TABLE statement:CREATE TABLE Persons(P_Id int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City columns are of type varchar with a maximum length of 255 characters.The empty "Persons" table will now look like this:P_IdLastNameFirstNameAddressCity?????The empty table can be filled with data with the INSERT INTO statement.SQL PRIMARY KEY ConstraintThe PRIMARY KEY constraint uniquely identifies each record in a database table.Primary keys must contain unique values.A primary key column cannot contain NULL values.Each table should have a primary key, and each table can have only ONE primary key.SQL PRIMARY KEY Constraint on CREATE TABLEThe following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:MySQL:CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (P_Id))SQL Server / Oracle / MS Access:CREATE TABLE Persons(P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:MySQL / SQL Server / Oracle / MS Access:CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName))Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).SQL PRIMARY KEY Constraint on ALTER TABLETo create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:MySQL / SQL Server / Oracle / MS Access:ALTER TABLE PersonsADD PRIMARY KEY (P_Id)To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:MySQL / SQL Server / Oracle / MS Access:ALTER TABLE PersonsADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).To DROP a PRIMARY KEY ConstraintTo drop a PRIMARY KEY constraint, use the following SQL:MySQL:ALTER TABLE PersonsDROP PRIMARY KEYSQL Server / Oracle / MS Access:ALTER TABLE PersonsDROP CONSTRAINT pk_PersonIDSQL FOREIGN KEY ConstraintSQL FOREIGN KEY ConstraintA FOREIGN KEY in one table points to a PRIMARY KEY in another table.Let's illustrate the foreign key with an example. Look at the following two tables:The "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe "Orders" table:O_IdOrderNoP_Id1778953244678332245624245621Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.SQL FOREIGN KEY Constraint on CREATE TABLEThe following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created:MySQL:CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,P_Id int,PRIMARY KEY (O_Id),FOREIGN KEY (P_Id) REFERENCES Persons(P_Id))SQL Server / Oracle / MS Access:CREATE TABLE Orders(O_Id int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id))To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:MySQL / SQL Server / Oracle / MS Access:CREATE TABLE Orders(O_Id int NOT NULL,OrderNo int NOT NULL,P_Id int,PRIMARY KEY (O_Id),CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)REFERENCES Persons(P_Id))SQL FOREIGN KEY Constraint on ALTER TABLETo create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:MySQL / SQL Server / Oracle / MS Access:ALTER TABLE OrdersADD FOREIGN KEY (P_Id)REFERENCES Persons(P_Id)To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:MySQL / SQL Server / Oracle / MS Access:ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES Persons(P_Id)To DROP a FOREIGN KEY ConstraintTo drop a FOREIGN KEY constraint, use the following SQL:MySQL:ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrdersSQL Server / Oracle / MS Access:ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrdersSQL StatementsMost of the actions you need to perform on a database are done with SQL statements.The following SQL statement will select all the records in the "Persons" table:SELECT * FROM PersonsIn this tutorial we will teach you all about the different SQL statements.Keep in Mind That...SQL is not case sensitiveSemicolon after SQL Statements?Some database systems require 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.We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.SQL DML and DDLSQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).The query and update commands form the DML part of SQL:SELECT - extracts data from a databaseUPDATE - updates data in a databaseDELETE - deletes data from a databaseINSERT INTO - inserts new data into a databaseThe DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:CREATE DATABASE - creates a new databaseALTER DATABASE - modifies a databaseCREATE TABLE - creates a new tableALTER TABLE - modifies a tableDROP TABLE - deletes a tableCREATE INDEX - creates an index (search key)DROP INDEX - deletes an index SQL SELECT StatementThis chapter will explain the SELECT and the SELECT * statements.The SQL SELECT StatementThe SELECT statement is used to select data from a database.The result is stored in a result table, called the result-set.SQL SELECT SyntaxSELECT column_name(s)FROM table_nameandSELECT * FROM table_nameNote: SQL is not case sensitive. SELECT is the same as select.An SQL SELECT ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select the content of the columns named "LastName" and "FirstName" from the table above.We use the following SELECT statement:SELECT LastName,FirstName FROM PersonsThe result-set will look like this:LastNameFirstNameHansenOlaSvendsonTovePettersenKariSELECT * ExampleNow we want to select all the columns from the "Persons" table.We use the following SELECT statement:?SELECT * FROM PersonsTip: The asterisk (*) is a quick way of selecting all columns!The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNavigation in a Result-setMost database software systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our ADO tutorial or our PHP tutorial.SQL SELECT DISTINCT StatementThis chapter will explain the SELECT DISTINCT statement.The SQL SELECT DISTINCT StatementIn a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.The DISTINCT keyword can be used to return only distinct (different) values.SQL SELECT DISTINCT SyntaxSELECT DISTINCT column_name(s)FROM table_nameSELECT DISTINCT ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select only the distinct values from the column named "City" from the table above.We use the following SELECT statement:SELECT DISTINCT City FROM PersonsThe result-set will look like this:CitySandnesStavangerThe WHERE clause is used to filter records.The WHERE Clause?The WHERE clause is used to extract only those records that fulfill a specified criterion.SQL WHERE SyntaxSELECT column_name(s)FROM table_nameWHERE column_name operator valueWHERE Clause ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select only the persons living in the city "Sandnes" from the table above.We use the following SELECT statement:SELECT * FROM PersonsWHERE City='Sandnes'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesQuotes Around Text FieldsSQL uses single quotes around text values (most database systems will also accept double quotes).Although, numeric values should not be enclosed in quotes.For text values:This is correct:SELECT * FROM Persons WHERE FirstName='Tove'This is wrong:SELECT * FROM Persons WHERE FirstName=ToveFor numeric values:This is correct:SELECT * FROM Persons WHERE Year=1965This is wrong:SELECT * FROM Persons WHERE Year='1965'Operators Allowed in the WHERE ClauseWith the WHERE clause, the following operators can be used:OperatorDescription=Equal<>Not equal>Greater than<Less than>=Greater than or equal<=Less than or equalBETWEENBetween an inclusive rangeLIKESearch for a patternINIf you know the exact value you want to return for at least one of the columnsNote: In some versions of SQL the <> operator may be written as !=SQL AND & OR OperatorsThe AND & OR operators are used to filter records based on more than one condition.The AND & OR OperatorsThe AND operator displays a record if both the first condition and the second condition is true.The OR operator displays a record if either the first condition or the second condition is true.AND Operator ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select only the persons with the first name equal to "Tove" AND the last name equal to "Svendson":We use the following SELECT statement:SELECT * FROM PersonsWHERE FirstName='Tove'AND LastName='Svendson'The result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23SandnesOR Operator ExampleNow we want to select only the persons with the first name equal to "Tove" OR the first name equal to "Ola":We use the following SELECT statement:SELECT * FROM PersonsWHERE FirstName='Tove'OR FirstName='Ola'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesCombining AND & ORYou can also combine AND and OR (use parenthesis to form complex expressions).Now we want to select only the persons with the last name equal to "Svendson" AND the first name equal to "Tove" OR to "Ola":We use the following SELECT statement:SELECT * FROM Persons WHERELastName='Svendson'AND (FirstName='Tove' OR FirstName='Ola')The result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23SandnesSQL ORDER BY KeywordThe ORDER BY keyword is used to sort the result-set.The ORDER BY KeywordThe ORDER BY keyword is used to sort the result-set by a specified column.The ORDER BY keyword sort the records in ascending order by default.If you want to sort the records in a descending order, you can use the DESC keyword.SQL ORDER BY SyntaxSELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESCORDER BY ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenTomVingvn 23StavangerNow we want to select all the persons from the table above, however, we want to sort the persons by their last name.We use the following SELECT statement:SELECT * FROM PersonsORDER BY LastNameThe result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes4NilsenTomVingvn 23Stavanger3PettersenKariStorgt 20Stavanger2SvendsonToveBorgvn 23SandnesORDER BY DESC ExampleNow we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.We use the following SELECT statement:SELECT * FROM PersonsORDER BY LastName DESCThe result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenTomVingvn 23Stavanger1HansenOlaTimoteivn 10SandnesSQL INSERT INTO Statement? PreviousNext Chapter ?The INSERT INTO statement is used to insert new records in a table.The INSERT INTO StatementThe INSERT INTO statement is used to insert a new row in a table.SQL INSERT INTO SyntaxIt is possible to write the INSERT INTO statement in two forms. The first form doesn't specify the column names where the data will be inserted, only their values:INSERT INTO table_nameVALUES (value1, value2, value3,...)The second form specifies both the column names and the values to be inserted:INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)SQL INSERT INTO ExampleWe have the following "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to insert a new row in the "Persons" table.We use the following SQL statement:INSERT INTO PersonsVALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')The "Persons" table will now look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2StavangerInsert Data Only in Specified ColumnsIt is also possible to only add data in specific columns.The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the "FirstName" columns:INSERT INTO Persons (P_Id, LastName, FirstName)VALUES (5, 'Tjessem', 'Jakob')The "Persons" table will now look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2Stavanger5TjessemJakob??SQL UPDATE StatementThe UPDATE statement is used to update records in a table.The UPDATE StatementThe UPDATE statement is used to update existing records in a table.SQL UPDATE SyntaxUPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_valueNote: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!SQL UPDATE ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2Stavanger5TjessemJakob??Now we want to update the person "Tjessem, Jakob" in the "Persons" table.We use the following SQL statement:UPDATE PersonsSET Address='Nissestien 67', City='Sandnes'WHERE LastName='Tjessem' AND FirstName='Jakob'The "Persons" table will now look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2Stavanger5TjessemJakobNissestien 67SandnesSQL UPDATE WarningBe careful when updating records. If we had omitted the WHERE clause in the example above, like this:UPDATE PersonsSET Address='Nissestien 67', City='Sandnes'The "Persons" table would have looked like this:P_IdLastNameFirstNameAddressCity1HansenOlaNissestien 67Sandnes2SvendsonToveNissestien 67Sandnes3PettersenKariNissestien 67Sandnes4NilsenJohanNissestien 67Sandnes5TjessemJakobNissestien 67SandnesSQL DELETE StatementThe DELETE statement is used to delete records in a table.The DELETE StatementThe DELETE statement is used to delete rows in a table.SQL DELETE SyntaxDELETE FROM table_nameWHERE some_column=some_valueNote: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!SQL DELETE ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2Stavanger5TjessemJakobNissestien 67SandnesNow we want to delete the person "Tjessem, Jakob" in the "Persons" table.We use the following SQL statement:DELETE FROM PersonsWHERE LastName='Tjessem' AND FirstName='Jakob'The "Persons" table will now look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20Stavanger4NilsenJohanBakken 2StavangerDelete All RowsIt is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:DELETE FROM table_nameorDELETE * FROM table_nameNote: Be very careful when deleting records. You cannot undo this statement!Try it YourselfTo see how SQL works, you can copy the SQL statements below and paste them into the textarea, or you can make your own SQL statements.SELECT * FROM customersSELECT CompanyName, ContactName FROM customersSELECT * FROM customers WHERE companyname LIKE 'a%'SELECT CompanyName, ContactNameFROM customersWHERE CompanyName > 'a'When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).SELECT CompanyName, ContactNameFROM customersWHERE CompanyName > 'g'AND ContactName > 'g'Top of FormBottom of FormSQL LIKE OperatorThe LIKE operator is used in a WHERE clause to search for a specified pattern in a column.The LIKE OperatorThe LIKE operator is used to search for a specified pattern in a column.SQL LIKE SyntaxSELECT column_name(s)FROM table_nameWHERE column_name LIKE patternLIKE Operator ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select the persons living in a city that starts with "s" from the table above.We use the following SELECT statement:SELECT * FROM PersonsWHERE City LIKE 's%'The "%" sign can be used to define wildcards (missing letters in the pattern)?both before and after the pattern.The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNext, we want to select the persons living in a city that ends with an "s" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE City LIKE '%s'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesNext, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE City LIKE '%tav%'The result-set will look like this:P_IdLastNameFirstNameAddressCity3PettersenKariStorgt 20StavangerIt is also possible to select the persons living in a city that does NOT contain the pattern "tav" from the "Persons" table, by using the NOT keyword.We use the following SELECT statement:SELECT * FROM PersonsWHERE City NOT LIKE '%tav%'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesSQL WildcardsSQL wildcards can be used when searching for data in a database.SQL Wildcards?SQL wildcards can substitute for one or more characters when searching for data in a database.SQL wildcards must be used with the SQL LIKE operator.With SQL, the following wildcards can be used:WildcardDescription%A substitute for zero or more characters _A substitute for exactly one character[charlist]Any single character in charlist[^charlist] or[!charlist]Any single character not in charlistSQL Wildcard ExamplesWe have the following "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerUsing the % WildcardNow we want to select the persons living in a city that starts with "sa" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE City LIKE 'sa%'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesNext, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE City LIKE '%nes%'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23SandnesUsing the _ WildcardNow we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE FirstName LIKE '_la'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10SandnesNext, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE LastName LIKE 'S_end_on'The result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23SandnesUsing the [charlist] WildcardNow we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE LastName LIKE '[bsp]%'The result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNext, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.We use the following SELECT statement:SELECT * FROM PersonsWHERE LastName LIKE '[!bsp]%'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10SandnesSQL IN OperatorThe IN OperatorThe IN operator allows you to specify multiple values in a WHERE clause.SQL IN SyntaxSELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)IN Operator ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the table above.We use the following SELECT statement:SELECT * FROM PersonsWHERE LastName IN ('Hansen','Pettersen')The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes3PettersenKariStorgt 20StavangerSQL BETWEEN OperatorThe BETWEEN operator is used in a WHERE clause to select a range of data between two values.The BETWEEN OperatorThe BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.SQL BETWEEN SyntaxSELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2BETWEEN Operator ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNow we want to select the persons with a last name alphabetically between "Hansen" and "Pettersen" from the table above.We use the following SELECT statement:SELECT * FROM PersonsWHERE LastNameBETWEEN 'Hansen' AND 'Pettersen'The result-set will look like this:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10SandnesNote: The BETWEEN operator is treated differently in different databases!In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values.In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the BETWEEN operator selects fields that are between and including the test values.And in other databases, persons with the LastName of "Hansen" will be listed, but "Pettersen" will not be listed (like the example above), because the BETWEEN operator selects fields between the test values, including the first test value and excluding the last test value.Therefore: Check how your database treats the BETWEEN operator.Example 2To display the persons outside the range in the previous example, use NOT BETWEEN:SELECT * FROM PersonsWHERE LastNameNOT BETWEEN 'Hansen' AND 'Pettersen'The result-set will look like this:P_IdLastNameFirstNameAddressCity2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerSQL JoinsSQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.SQL JOINThe JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.Tables in a database are often related to each other with keys.A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.Look at the "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerNote that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.Next, we have the "Orders" table:O_IdOrderNoP_Id177895324467833224561424562153476415Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.Notice that the relationship between the two tables above is the "P_Id" column.Different SQL JOINsBefore we continue with examples, we will list the types of JOIN you can use, and the differences between them.JOIN: Return rows when there is at least one match in both tablesLEFT JOIN: Return all rows from the left table, even if there are no matches in the right tableRIGHT JOIN: Return all rows from the right table, even if there are no matches in the left tableFULL JOIN: Return rows when there is a match in one of the tablesSQL INNER JOIN Keyword? PreviousNext Chapter ?SQL INNER JOIN KeywordThe INNER JOIN keyword return rows when there is at least one match in both tables.SQL INNER JOIN SyntaxSELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1.column_name=table_name2.column_namePS: INNER JOIN is the same as JOIN.SQL INNER JOIN ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe "Orders" table:O_IdOrderNoP_Id177895324467833224561424562153476415Now we want to list all the persons with any orders.We use the following SELECT statement:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.P_Id=Orders.P_IdORDER BY Persons.LastNameThe result-set will look like this:LastNameFirstNameOrderNoHansenOla22456HansenOla24562PettersenKari77895PettersenKari44678The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.SQL LEFT JOIN KeywordSQL LEFT JOIN KeywordThe LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).SQL LEFT JOIN SyntaxSELECT column_name(s)FROM table_name1LEFT JOIN table_name2ON table_name1.column_name=table_name2.column_namePS: In some databases LEFT JOIN is called LEFT OUTER JOIN.SQL LEFT JOIN ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe "Orders" table:O_IdOrderNoP_Id177895324467833224561424562153476415Now we want to list all the persons and their orders - if any, from the tables above.We use the following SELECT statement:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsLEFT JOIN OrdersON Persons.P_Id=Orders.P_IdORDER BY Persons.LastNameThe result-set will look like this:LastNameFirstNameOrderNoHansenOla22456HansenOla24562PettersenKari77895PettersenKari44678SvendsonTove?The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).SQL RIGHT JOIN KeywordSQL RIGHT JOIN KeywordThe RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).SQL RIGHT JOIN SyntaxSELECT column_name(s)FROM table_name1RIGHT JOIN table_name2ON table_name1.column_name=table_name2.column_namePS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.SQL RIGHT JOIN ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe "Orders" table:O_IdOrderNoP_Id177895324467833224561424562153476415Now we want to list all the orders with containing persons - if any, from the tables above.We use the following SELECT statement:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsRIGHT JOIN OrdersON Persons.P_Id=Orders.P_IdORDER BY Persons.LastNameThe result-set will look like this:LastNameFirstNameOrderNoHansenOla22456HansenOla24562PettersenKari77895PettersenKari44678??34764The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).SQL FULL JOIN KeywordSQL FULL JOIN KeywordThe FULL JOIN keyword return rows when there is a match in one of the tables.SQL FULL JOIN SyntaxSELECT column_name(s)FROM table_name1FULL JOIN table_name2ON table_name1.column_name=table_name2.column_nameSQL FULL JOIN ExampleThe "Persons" table:P_IdLastNameFirstNameAddressCity1HansenOlaTimoteivn 10Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKariStorgt 20StavangerThe "Orders" table:O_IdOrderNoP_Id177895324467833224561424562153476415Now we want to list all the persons and their orders, and all the orders with their persons.We use the following SELECT statement:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsFULL JOIN OrdersON Persons.P_Id=Orders.P_IdORDER BY Persons.LastNameThe result-set will look like this:LastNameFirstNameOrderNoHansenOla22456HansenOla24562PettersenKari77895PettersenKari44678SvendsonTove???34764The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.SQL UNION OperatorThe SQL UNION operator combines two or more SELECT statements.The SQL UNION OperatorThe UNION operator is used to combine the result-set of two or more SELECT statements.Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.SQL UNION SyntaxSELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.SQL UNION ALL SyntaxSELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.SQL UNION ExampleLook at the following tables:"Employees_Norway":E_IDE_Name01Hansen, Ola02Svendson, Tove03Svendson, Stephen04Pettersen, Kari"Employees_USA":E_IDE_Name01Turner, Sally02Kent, Clark03Svendson, Stephen04Scott, StephenNow we want to list all the different employees in Norway and USA.We use the following SELECT statement:SELECT E_Name FROM Employees_NorwayUNIONSELECT E_Name FROM Employees_USAThe result-set will look like this:E_NameHansen, OlaSvendson, ToveSvendson, StephenPettersen, KariTurner, SallyKent, ClarkScott, StephenNote: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them will be listed. The UNION command selects only distinct values.SQL UNION ALL ExampleNow we want to list all employees in Norway and USA:SELECT E_Name FROM Employees_NorwayUNION ALLSELECT E_Name FROM Employees_USAResultE_NameHansen, OlaSvendson, ToveSvendson, StephenPettersen, KariTurner, SallyKent, ClarkSvendson, StephenScott, StephenSQL FunctionsSQL has many built-in functions for performing calculations on data.SQL Aggregate FunctionsSQL aggregate functions return a single value, calculated from values in a column.Useful aggregate functions:AVG() - Returns the average valueCOUNT() - Returns the number of rowsFIRST() - Returns the first valueLAST() - Returns the last valueMAX() - Returns the largest valueMIN() - Returns the smallest valueSUM() - Returns the sumSQL Scalar functionsSQL scalar functions return a single value, based on the input value.Useful scalar functions:UCASE() - Converts a field to upper caseLCASE() - Converts a field to lower caseMID() - Extract characters from a text fieldLEN() - Returns the length of a text fieldROUND() - Rounds a numeric field to the number of decimals specifiedNOW() - Returns the current system date and timeFORMAT() - Formats how a field is to be displayedTip: The aggregate functions and the scalar functions will be explained in details in the next chapters.SQL AVG() FunctionThe AVG() FunctionThe AVG() function returns the average value of a numeric column.SQL AVG() SyntaxSELECT AVG(column_name) FROM table_nameSQL AVG() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the average value of the "OrderPrice" fields.We use the following SQL statement:SELECT AVG(OrderPrice) AS OrderAverage FROM OrdersThe result-set will look like this:OrderAverage950Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.We use the following SQL statement:SELECT Customer FROM OrdersWHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)The result-set will look like this:CustomerHansenNilsenJensenSQL COUNT() FunctionThe COUNT() function returns the number of rows that matches a specified criteria.SQL COUNT(column_name) SyntaxThe COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:SELECT COUNT(column_name) FROM table_nameSQL COUNT(*) SyntaxThe COUNT(*) function returns the number of records in a table:SELECT COUNT(*) FROM table_nameSQL COUNT(DISTINCT column_name) SyntaxThe COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:SELECT COUNT(DISTINCT column_name) FROM table_nameNote:COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.SQL COUNT(column_name) ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to count the number of orders from "Customer Nilsen".We use the following SQL statement:SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Nilsen'The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:CustomerNilsen2SQL COUNT(*) ExampleIf we omit the WHERE clause, like this:SELECT COUNT(*) AS NumberOfOrders FROM OrdersThe result-set will look like this:NumberOfOrders6which is the total number of rows in the table.SQL COUNT(DISTINCT column_name) ExampleNow we want to count the number of unique customers in the "Orders" table.We use the following SQL statement:SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM OrdersThe result-set will look like this:NumberOfCustomers3which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.SQL FIRST() FunctionThe FIRST() FunctionThe FIRST() function returns the first value of the selected column.SQL FIRST() SyntaxSELECT FIRST(column_name) FROM table_nameSQL FIRST() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the first value of the "OrderPrice" column.We use the following SQL statement:SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM OrdersTip: Workaround if FIRST() function is not supported:SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1The result-set will look like this:FirstOrderPrice1000SQL LAST() FunctionThe LAST() FunctionThe LAST() function returns the last value of the selected column.SQL LAST() SyntaxSELECT LAST(column_name) FROM table_nameSQL LAST() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the last value of the "OrderPrice" column.We use the following SQL statement:SELECT LAST(OrderPrice) AS LastOrderPrice FROM OrdersTip: Workaround if LAST() function is not supported:SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1The result-set will look like this:LastOrderPrice100SQL MAX() FunctionThe MAX() FunctionThe MAX() function returns the largest value of the selected column.SQL MAX() SyntaxSELECT MAX(column_name) FROM table_nameSQL MAX() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the largest value of the "OrderPrice" column.We use the following SQL statement:SELECT MAX(OrderPrice) AS LargestOrderPrice FROM OrdersThe result-set will look like this:LargestOrderPrice2000SQL MIN() FunctionThe MIN() FunctionThe MIN() function returns the smallest value of the selected column.SQL MIN() SyntaxSELECT MIN(column_name) FROM table_nameSQL MIN() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the smallest value of the "OrderPrice" column.We use the following SQL statement:SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM OrdersThe result-set will look like this:SmallestOrderPrice100SQL SUM() FunctionThe SUM() FunctionThe SUM() function returns the total sum of a numeric column.SQL SUM() SyntaxSELECT SUM(column_name) FROM table_nameSQL SUM() ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the sum of all "OrderPrice" fields".We use the following SQL statement:SELECT SUM(OrderPrice) AS OrderTotal FROM OrdersThe result-set will look like this:OrderTotal5700SQL GROUP BY StatementAggregate functions often need an added GROUP BY statement.The GROUP BY StatementThe GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.SQL GROUP BY SyntaxSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name SQL GROUP BY ExampleWe have the following "Orders" table:O_IdOrderDateOrderPriceCustomer12008/11/121000Hansen22008/10/231600Nilsen32008/09/02700Hansen42008/09/03300Hansen52008/08/302000Jensen62008/10/04100NilsenNow we want to find the total sum (total order) of each customer.We will have to use the GROUP BY statement to group the customers.We use the following SQL statement:SELECT Customer,SUM(OrderPrice) FROM OrdersGROUP BY CustomerThe result-set will look like this:CustomerSUM(OrderPrice)Hansen2000Nilsen1700Jensen2000Nice! Isn't it? :)Let's see what happens if we omit the GROUP BY statement:SELECT Customer,SUM(OrderPrice) FROM OrdersThe result-set will look like this:CustomerSUM(OrderPrice)Hansen5700Nilsen5700Hansen5700Hansen5700Jensen5700Nilsen5700The result-set above is not what we wanted.Explanation of why the above SELECT statement cannot be used: The SELECT statement above has two columns specified (Customer and SUM(OrderPrice). The "SUM(OrderPrice)" returns a single value (that is the total sum of the "OrderPrice" column), while "Customer" returns 6 values (one value for each row in the "Orders" table). This will therefore not give us the correct result. However, you have seen that the GROUP BY statement solves this problem.GROUP BY More Than One ColumnWe can also use the GROUP BY statement on more than one column, like this:SELECT Customer,OrderDate,SUM(OrderPrice) FROM OrdersGROUP BY Customer,OrderDateSQL Quick ReferenceFrom W3SchoolsSQL StatementSyntaxAND / ORSELECT column_name(s)FROM table_nameWHERE conditionAND|OR conditionALTER TABLEALTER TABLE table_name ADD column_name datatype orALTER TABLE table_name DROP COLUMN column_nameAS (alias)SELECT column_name AS column_aliasFROM table_name orSELECT column_nameFROM table_name? AS table_aliasBETWEENSELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2CREATE DATABASECREATE DATABASE database_nameCREATE TABLECREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name2 data_type,...)CREATE INDEXCREATE INDEX index_nameON table_name (column_name) orCREATE UNIQUE INDEX index_nameON table_name (column_name)CREATE VIEWCREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE conditionDELETEDELETE FROM table_nameWHERE some_column=some_value orDELETE FROM table_name (Note: Deletes the entire table!!)DELETE * FROM table_name (Note: Deletes the entire table!!)DROP DATABASEDROP DATABASE database_nameDROP INDEXDROP INDEX table_name.index_name (SQL Server)DROP INDEX index_name ON table_name (MS Access)DROP INDEX index_name (DB2/Oracle)ALTER TABLE table_nameDROP INDEX index_name (MySQL)DROP TABLEDROP TABLE table_nameGROUP BYSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVINGSELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator valueINSELECT column_name(s)FROM table_nameWHERE column_nameIN (value1,value2,..)INSERT INTOINSERT INTO table_nameVALUES (value1, value2, value3,....)orINSERT INTO table_name(column1, column2, column3,...)VALUES (value1, value2, value3,....)INNER JOINSELECT column_name(s)FROM table_name1INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_nameLEFT JOINSELECT column_name(s)FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_nameRIGHT JOINSELECT column_name(s)FROM table_name1RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_nameFULL JOINSELECT column_name(s)FROM table_name1FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_nameLIKESELECT column_name(s)FROM table_nameWHERE column_name LIKE patternORDER BYSELECT column_name(s)FROM table_nameORDER BY column_name [ASC|DESC]SELECTSELECT column_name(s)FROM table_nameSELECT *SELECT *FROM table_nameSELECT DISTINCTSELECT DISTINCT column_name(s)FROM table_nameSELECT INTOSELECT *INTO new_table_name [IN externaldatabase]FROM old_table_name orSELECT column_name(s)INTO new_table_name [IN externaldatabase]FROM old_table_nameSELECT TOPSELECT TOP number|percent column_name(s)FROM table_nameTRUNCATE TABLETRUNCATE TABLE table_nameUNIONSELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2UNION ALLSELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2UPDATEUPDATE table_nameSET column1=value, column2=value,...WHERE some_column=some_valueWHERESELECT column_name(s)FROM table_nameWHERE column_name operator valueSource : SummaryThis SQL tutorial has taught you the standard computer language for accessing and manipulating database systems.You have learned how to execute queries, retrieve data, insert new records, delete records and update records in a database with SQL.You have also learned how to create databases, tables, and indexes with SQL, and how to drop them.You have learned the most important aggregate functions in SQL.You now know that SQL is the standard language that works with all the well-known database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and MS Access.Taken from: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download