A view is a virtual table. This chapter shows how to create, update, and delete a view. 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…
Auto-increment allows a unique number to be generated when a new record is inserted into a table. AUTO INCREMENT a Field Very often we would like the value of the primary key field to be created automatically every time a new record is inserted. We would like to create an auto-increment field in a table. 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…
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. SQL ALTER TABLE Syntax To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column in a table, use the following syntax:…
Indexes, tables, and databases can easily be deleted/removed with the DROP statement. The DROP INDEX Statement The DROP INDEX statement is used to delete an index in a table. DROP INDEX Syntax for MS Access: DROP INDEX index_name ON table_name DROP INDEX Syntax for MS SQL Server: DROP INDEX table_name.index_name DROP INDEX Syntax for DB2/Oracle: DROP INDEX index_name DROP INDEX Syntax for MySQL: ALTER TABLE table_name DROP INDEX index_name The DROP TABLE Statement The DROP TABLE statement is…
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. SQL DEFAULT Constraint on CREATE TABLE The following SQL creates a DEFAULT constraint on the “City” column when the “Persons” table is created: My SQL / 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…
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 Constraint on CREATE TABLE The following SQL creates a CHECK constraint on the…
SQL FOREIGN KEY Constraint A 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_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger The “Orders” table: O_Id OrderNo P_Id 1 77895 3 2 44678 3 3 22456 2 4 24562 1 Note that the…
SQL PRIMARY KEY Constraint The 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 TABLE The 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,…
The IN Operator The IN operator allows you to specify multiple values in a WHERE clause. SQL IN Syntax SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,…); Example SELECT * FROM Customers WHERE City IN (‘Paris’,’London’);
A wildcard character can be used to substitute for any other character(s) in a string. SQL Wildcard Characters In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. With SQL, the wildcards are: Wildcard Description % A substitute for zero or more characters _ A substitute for a single character Matches only a character NOT specified…
