DBTECH

SQL Quiz You can test your SQL skills with W3Schools' Quiz. The Test The test contains 25 questions and there is no time limit. The test is not official, it's just a nice way to see how much you know, or don't know, about SQL. Count Your Score You will get 1 point for each correct answer. At the end of the Quiz, your total score will be displayed. Maximum score is 25 points.

SQL Quick Reference   SQL Statement Syntax AND / OR SELECT column_name(s) FROM table_name WHERE condition AND|OR condition ALTER TABLE ALTER TABLE table_name ADD column_name datatypeor ALTER TABLE table_name DROP COLUMN column_name AS (alias) SELECT column_name AS column_alias FROM table_nameor SELECT column_name FROM table_name  AS table_alias BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 CREATE DATABASE CREATE DATABASE database_name CREATE TABLE CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, ... ) CREATE INDEX CREATE INDEX index_name ON table_name (column_name)or CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE VIEW CREAT

SQL Data Types for Various DBs Data types and ranges for Microsoft Access, MySQL and SQL Server. Microsoft Access Data Types Data type Description Storage Text Use for text or combinations of text and numbers. 255 characters maximum   Memo Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable   Byte Allows whole numbers from 0 to 255 1 byte Integer Allows whole numbers between -32,768 and 32,767 2 bytes Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes Single Single precision floating-point. Will handle most decimals 4 bytes Double Double precision floating-point. Will handle most decimals 8 bytes Currency Use for currency. Holds

SQL General Data Types A data type defines what kind of value a column can contain. SQL General Data Types Each column in a database table is required to have a name and a data type. SQL developers have to decide what types of data will be stored inside each and every table column when creating a SQL table. The data type is a label and a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data. The following table lists the general data types in SQL: Data type Description CHARACTER(n) Character string. Fixed-length n VARCHAR(n) or CHARACTER VARYING(n) Character string. Variable length. Maximum length n BINARY(n) Binary string. Fixed-length n BOOLEAN Stores TRUE or FALSE values VARBINARY(n) or BINARY VARYING(n) Binary string. Variable length. Maximum l

SQL Operators

SQL NULL Functions SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions Look at the following "Products" table: P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23   3 Gorgonzola 15.67 9 20 Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values. We have the following SELECT statement: SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM Products In the example above, if any of the "UnitsOnOrder" values are NULL, the result is NULL. Microsoft's ISNULL() function is used to specify how we want to treat NULL values. The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result. In this case we want NULL values to be zero. Below, if "UnitsOnOrder" is NULL it will not harm the calcu

SQL Date Functions MySQL Date Functions The following table lists the most important built-in date functions in MySQL: Function Description NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns the current time DATE() Extracts the date part of a date or date/time expression EXTRACT() Returns a single part of a date/time DATE_ADD() Adds a specified time interval to a date DATE_SUB() Subtracts a specified time interval from a date DATEDIFF() Returns the number of days between two dates DATE_FORMAT() Displays date/time data in different formats SQL Server Date Functions The following table lists the most important built-in date functions in SQL Server: Function Description GETDATE() Returns the current date and time DATEPART() Returns a single part of a date/time DATEADD() Adds or subtracts a specified time interval from a date DATEDIFF() Returns the time between two

SQL Functions SQL has many built-in functions for performing calculations on data. SQL Aggregate Functions SQL aggregate functions return a single value, calculated from values in a column. Function Description AVG() Returns the average value COUNT() Returns the number of rows FIRST() Returns the first value LAST() Returns the last value MAX() Returns the largest value MIN() Returns the smallest value ROUND() Rounds a numeric field to the number of decimals specified SUM() Returns the sum SQL String Functions Function Description CHARINDEX Searches an expression in a string expression and returns its starting position if found CONCAT()   LEFT()   LEN() / LENGTH() Returns the length of the value in a text field LOWER() / LCASE() Converts character data to lower case LTRIM()   SUBSTRING() / MID() Extract characters from a text field PATINDEX()   REPLACE()   RIGHT()   RTRIM()  

SQL Hosting SQL Hosting If you want your web site to be able to store and retrieve data from a database, your web server should have access to a database-system that uses the SQL language. If your web server is hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans. The most common SQL hosting databases are MS SQL Server, Oracle, MySQL, and MS Access. MS SQL Server Microsoft's SQL Server is a popular database software for database-driven web sites with high traffic. SQL Server is a very powerful, robust and full featured SQL database system. Oracle Oracle is also a popular database software for database-driven web sites with high traffic. Oracle is a very powerful, robust and full featured SQL database system. MySQL MySQL is also a popular database software for web sites. MySQL is a very powerful, robust and full featured SQL database system. MySQL is an inexpensive alternative to the expensive Micro

SQL Injection SQL Injection SQL injection is a code injection technique that might destroy your database. SQL injection is one of the most common web hacking techniques. SQL injection is the placement of malicious code in SQL statements, via web page input. SQL in Web Pages SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user give you an SQL statement that you will unknowingly run on your database. Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString): Example txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId; The rest of this chapter describes the potential dangers of using user input in SQL statements. SQL Injection Based on 1=1 is Always True Look at the example above again. The orig

SQL Views SQL CREATE VIEW Statement In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view. SQL CREATE VIEW Examples If you have the Northwind database you can see that it has several views installed by default. The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:

SQL AUTO INCREMENT Field AUTO INCREMENT Field Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted. Syntax for MySQL The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table: CREATE TABLE Persons (     ID int NOT NULL AUTO_INCREMENT,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int,     PRIMARY KEY (ID) ); MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement: ALTER TABLE Persons AUTO_INCREMENT= 100 ; To insert a new record into

SQL CREATE INDEX Statement SQL CREATE INDEX Statement The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against. CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed: CREATE INDEX index_name ON table_name ( column1 , column2 , ...); CREATE UNIQUE INDEX Syntax Creates a unique index on a table. Duplicate values are not allowed: CREATE UNIQUE INDEX index_name ON table_name ( column1 , column2 , ...); Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database. CREATE INDEX Example

SQL DEFAULT Constraint The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified. SQL DEFAULT on CREATE TABLE The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created: My SQL / SQL Server / Oracle / MS Access: CREATE TABLE Persons (     ID int NOT NULL ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int,     City varchar( 255 ) DEFAULT 'Sandnes' ); The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE(): CREATE TABLE Orders (     ID int NOT NULL ,     OrderNumber int NOT NULL ,     OrderDate date DEFAULT GETDATE() ); SQL DEFAULT on ALTER TABLE To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL: MySQL: ALTER TABLE Persons ALT

SQL CHECK Constraint SQL CHECK Constraint The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. SQL CHECK on CREATE TABLE The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years: MySQL: CREATE TABLE Persons (     ID int NOT NULL ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int,     CHECK (Age>= 18 ) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons (     ID int NOT NULL ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int  CHECK (Age>= 18 ) ); To allow

SQL FOREIGN KEY Constraint SQL FOREIGN KEY Constraint A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table points to a PRIMARY KEY in another table. Look at the following two tables: "Persons" table: PersonID LastName FirstName Age 1 Hansen Ola 30 2 Svendson Tove 23 3 Pettersen Kari 20 "Orders" table: OrderID OrderNumber PersonID 1 77895 3 2 44678 3 3 22456 2 4 24562 1 Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table. The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "PersonID" column in the "Orders" table is a FOREIGN KEY

SQL PRIMARY KEY Constraint SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields. SQL PRIMARY KEY on CREATE TABLE The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created: MySQL: CREATE TABLE Persons (     ID int NOT NULL ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int,     PRIMARY KEY (ID) ); SQL Server / Oracle / MS Access: CREATE TABLE Persons (     ID int NOT NULL PRIMARY KEY ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int ); 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 / O

SQL UNIQUE Constraint SQL UNIQUE Constraint The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created: SQL Server / Oracle / MS Access: CREATE TABLE Persons (     ID int NOT NULL UNIQUE ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int ); MySQL: CREATE TABLE Persons (     ID int NOT NULL ,     LastName varchar( 255 ) NOT NULL ,     FirstName varchar( 255 ),     Age int,     UNIQUE (ID) ); To name a UNIQUE constraint, and to define a UNIQUE constrain