Main Body Show
Adrienne Watt & Nelson Eng Structured Query Language (SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers. Many of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server (shown in Figure 15.1), MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL. Figure 15.1. Example of Microsoft SQL Server, by A. Watt.In a DBMS, the SQL database language is used to:
In this chapter, we will focus on using SQL to create the database and table structures, mainly using SQL as a data definition language (DDL). In Chapter 16, we will use SQL as a data manipulation language (DML) to insert, delete, select and update data within the database tables. Create DatabaseThe major SQL DDL statements are CREATE DATABASE and CREATE/DROP/ALTER TABLE. The SQL statement CREATE is used to create the database and table structures. Example: CREATE DATABASE SW A new database named SW is created by the SQL statement CREATE DATABASE SW. Once the database is created, the next step is to create the database tables. The general format for the CREATE TABLE command is: CREATE TABLE <tablename> Tablename is the name of the database table such as Employee. Each field in the CREATE TABLE has three parts (see above):
ColumnNameThe ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName. Data TypeThe data type, as described below, must be a system data type or a user-defined data type. Many of the data types have a size such as CHAR(35) or Numeric(8,2). Bit –Integer data with either a 1 or 0 value Int –Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647) Smallint –Integer data from 2^15 (-32,768) through 2^15 – 1 (32,767) Tinyint –Integer data from 0 through 255 Decimal –Fixed precision and scale numeric data from -10^38 -1 through 10^38 Numeric –A synonym for decimal Timestamp –A database-wide unique number Uniqueidentifier –A globally unique identifier (GUID) Money – Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to one-ten-thousandth of a monetary unit Smallmoney –Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to one-ten-thousandth of a monetary unit Float –Floating precision number data from -1.79E + 308 through 1.79E + 308 Real –Floating precision number data from -3.40E + 38 through 3.40E + 38 Datetime –Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one-three-hundredths of a second, or 3.33 milliseconds Smalldatetime –Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute Char –Fixed-length non-Unicode character data with a maximum length of 8,000 characters Varchar –Variable-length non-Unicode data with a maximum of 8,000 characters Text –Variable-length non-Unicode data with a maximum length of 2^31 – 1 (2,147,483,647) characters Binary –Fixed-length binary data with a maximum length of 8,000 bytes Varbinary –Variable-length binary data with a maximum length of 8,000 bytes Image – Variable-length binary data with a maximum length of 2^31 – 1 (2,147,483,647) bytes Optional Column ConstraintsThe Optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created. To illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the employees table with 16 attributes or fields. USE SW The first field is EmployeeNo with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL). Similarly, the second field is DepartmentName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key: CONSTRAINT EmployeePK PRIMARY KEY(EmployeeNo) We will discuss the constraint property further later in this chapter. Likewise, we can create a Department table, a Project table and an Assignment table using the CREATE TABLE SQL DDL command as shown in the below example. USE SW In this example, a project table is created with seven fields: ProjectID, ProjectName, Department, MaxHours, StartDate, and EndDate. USE SW In this last example, an assignment table is created with three fields: ProjectID, EmployeeNumber, and HoursWorked. The assignment table is used to record who (EmployeeNumber) and how much time(HoursWorked) an employee worked on the particular project(ProjectID). USE SW Table ConstraintsTable constraints are identified by the CONSTRAINT keyword and can be used to implement various constraints described below. IDENTITY constraintWe can use the optional column constraint IDENTITY to provide a unique, incremental value for that column. Identity columns are often used with the PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a column with a tinyint, smallint, int, decimal or numeric data type. This constraint:
For IDENTITY[(seed, increment)]
We will use another database example to further illustrate the SQL DDL statements by creating the table tblHotel in this HOTEL database. CREATE TABLE tblHotel UNIQUE constraint The UNIQUE constraint prevents duplicate values from being entered into a column.
This is the general syntax for the UNIQUE constraint: [CONSTRAINT constraint_name] This is an examle using the UNIQUE constraint. CREATE TABLE EMPLOYEES FOREIGN KEY constraintThe FOREIGN KEY (FK) constraint defines a column, or combination of columns, whose values match the PRIMARY KEY (PK) of another table.
This is the general syntax for the FOREIGN KEY constraint: [CONSTRAINT constraint_name] In this example, the field HotelNo in the tblRoom table is a FK to the field HotelNo in the tblHotel table shown previously. USE HOTEL CHECK constraintThe CHECK constraint restricts values that can be entered into a table.
This is the general syntax for the CHECK constraint: [CONSTRAINT constraint_name] In this example, the Type field is restricted to have only the types ‘Single’, ‘Double’, ‘Suite’ or ‘Executive’. USE HOTEL In this second example, the employee hire date should be before January 1, 2004, or have a salary limit of $300,000. GO DEFAULT constraintThe DEFAULT constraint is used to supply a value that is automatically added for a column if the user does not supply one.
The general syntax for the DEFAULT constraint is: [CONSTRAINT constraint_name] This example sets the default for the city field to ‘Vancouver’. USE HOTEL User Defined TypesUser defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls. To create a user-defined data type in SQL Server, choose types under “Programmability” in your database. Next, right click and choose ‘New’ –>‘User-defined data type’ or execute the sp_addtype system stored procedure. After this, type: sp_addtype ssn, ‘varchar(11)’, ‘NOT NULL’ This will add a new user-defined data type called SIN with nine characters. In this example, the field EmployeeSIN uses the user-defined data type SIN. CREATE TABLE SINTable ALTER TABLEYou can use ALTER TABLE statements to add and drop constraints.
In this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field. USE HOTEL Use the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName. ADD DROP TABLEThe DROP TABLE will remove a table from the database. Make sure you have the correct database selected. DROP TABLE tblHotel Executing the above SQL DROP TABLE statement will remove the table tblHotel from the database. DDL: abbreviation for data definition language DML: abbreviation for data manipulation language SEQUEL: acronym for Structured English Query Language; designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R Structured Query Language (SQL): a database language designed for managing data held in a relational database management system
Also see Appendix C: SQL Lab with Solution ReferencesDate, C.J. Relational Database Selected Writings. Reading: Mass: Addison-Wesley Publishing Company Inc., 1986, p. 269-311. What is meant by Structured Query Language?A relational data language that provides a consistent, English keyword-oriented set of facilities for query, data definition, data manipulation and data control. It is a programmed interface to relational database management systems (RDBMSs).
What is query language with example?Query languages, data query languages or database query languages (DQLs) are computer languages used to make queries in databases and information systems. A well known example is the Structured Query Language (SQL).
Why SQL is called structured language explain with an example?The original full name was SEQUEL, which stood for "Structured English Query Language". It later had to be renamed to SQL due to trademark issues. So basically, it was yet another attempt to sell a programming language as "just like English, except with a formal syntax" (hence "structured").
What are the Structured Query Language SQL commands?Four Categories of SQL Commands. Data Definition Language (DDL) This includes CREATE (tables, views, objects, etc.), ALTER and DROP (delete).. Data Manipulation Language (DML) SELECT, INSERT, UPDATE, DELETE of records within tables.. Data Control Language (DCL) ... . Database Indexing. ... . NOT NULL. ... . UNIQUE.. |