Module 2 - Database Administration PDF

Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH COMP 20213 – DATABASE ADMINISTRATION 0  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Mod...

Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH COMP 20213 – DATABASE ADMINISTRATION 0  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Module 2. Introduction to SQL Contents 2.1 Introduction to SQL 2.2 SQL Process 2.3 SQL RDBMS Databases 2.4 SQL Commands 2.4.1 DDL – Data Definition Language 2.4.2 DML – Data Manipulation Language 2.4.3 DCL – Data Control Language 2.5 Create Database and Drop Database 2.6 Create Table, Alter Table and Drop Table 2.7 SQL Constraints 2.7.1 NOT NULL Constraint 2.7.2 DEFAULT Constraint 2.7.3 UNIQUE Constraint 2.7.4 PRIMARY KEY Constraint 2.7.5 FOREIGN KEY Constraint 2.7.6 CHECK Constraint 2.8 Insert Query Objectives At the end of the course, the students should be able to: ✓ Understand what is SQL and its uses ✓ Describe the different RDBMS databases ✓ Learn the different DDL Commands by creating, dropping, and altering table using SQL Command ✓ Understand the importance and use of SQL Constraints ✓ Learn to create and drop database using SQL DDL Command ✓ Learn to create and alter tables using SQL Command ✓ Learn to insert new record into the database using SQL insert query COMP 20213 – DATABASE ADMINISTRATION 1  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987 SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. Why to Learn SQL? SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, they are using different dialects, such as − MS SQL Server using T-SQL, Oracle using PL/SQL, MS Access version of SQL is called JET SQL (native format) etc. Applications of SQL As mentioned before, SQL is one of the most widely used query language over the databases. I'm going to list few of them here: Allows users to access data in the relational database management systems. COMP 20213 – DATABASE ADMINISTRATION 2  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Allows users to describe the data. Allows users to define the data in a database and manipulate that data. Allows to embed within other languages using SQL modules, libraries & pre- compilers. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures and views. A Brief History of SQL 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases. 1974 − Structured Query Language appeared. 1978 − IBM worked to develop Codd's ideas and released a product named System/R. 1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle. SQL Process When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task. There are various components included in this process. These components are − Query Dispatcher Optimization Engines COMP 20213 – DATABASE ADMINISTRATION 3  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Classic Query Engine SQL Query Engine, etc. A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files. Following is a simple diagram showing the SQL Architecture − SQL - RDBMS Databases There are many popular RDBMS available to work with. This tutorial gives a brief overview of some of the most popular RDBMS’s. This would help you to compare their basic features. COMP 20213 – DATABASE ADMINISTRATION 4  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH MySQL MySQL is an open source SQL database, which is developed by a Swedish company – MySQL AB. MySQL is pronounced as "my ess-que-ell," in contrast with SQL, pronounced "sequel." MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X. MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server. History Development of MySQL by Michael Widenius & David Axmark beginning in 1994. First internal release on 23rd May 1995. Windows Version was released on the 8th January 1998 for Windows 95 and NT. Version 3.23: beta from June 2000, production release January 2001. Version 4.0: beta from August 2002, production release March 2003 (unions). Version 4.1: beta from June 2004, production release October 2004. Version 5.0: beta from March 2005, production release October 2005. Sun Microsystems acquired MySQL AB on the 26th February 2008. Version 5.1: production release 27th November 2008. Features High Performance. High Availability. Scalability and Flexibility Run anything. Robust Transactional Support. Web and Data Warehouse Strengths. Strong Data Protection. Comprehensive Application Development. Management Ease. Open Source Freedom and 24 x 7 Support. Lowest Total Cost of Ownership COMP 20213 – DATABASE ADMINISTRATION 5  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH MS SQL Server MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are − T-SQL ANSI SQL History 1987 - Sybase releases SQL Server for UNIX. 1988 - Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2. 1989 - Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2. 1990 - SQL Server 1.1 is released with support for Windows 3.0 clients. Aston - Tate drops out of SQL Server development. 2000 - Microsoft releases SQL Server 2000. 2001 - Microsoft releases XML for SQL Server Web Release 1 (download). 2002 - Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server). 2002 - Microsoft releases SQLXML 3.0. 2005 - Microsoft releases SQL Server 2005 on November 7th, 2005. Features High Performance DDL triggers High Availability Ranking functions Database mirroring Row version-based isolation levels Database snapshots XML integration CLR integration TRY...CATCH Service Broker Database Mail COMP 20213 – DATABASE ADMINISTRATION 6 ORACLE It is a very large multi-user based database management system. Oracle is a relational database management system developed by 'Oracle Corporation'. Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network. It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors. History Oracle began in 1977 and celebrating its 32 wonderful years in the industry (from 1977 to 2009). 1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work. 1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI). 1981 - RSI started developing tools for Oracle. 1982 - RSI was renamed to Oracle Corporation. 1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms. 1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc. 1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc. 2007 - Oracle released Oracle11g. The new version focused on better partitioning, easy migration, etc. Features Concurrency Locking Mechanisms Read Consistency Quiesce Database  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Portability Materialized views Self-managing database Bitmap indexes SQL*Plus Table compression ASM Parallel Execution Scheduler Analytic SQL Resource Manager Data mining Data Warehousing Partitioning MS ACCESS This is one of the most popular Microsoft products. Microsoft Access is an entry-level database management software. MS Access database is not only inexpensive but also a powerful database for small-scale projects. MS Access uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). MS Access comes with the professional edition of MS Office package. MS Access has easyto- use intuitive graphical interface. 1992 - Access version 1.0 was released. 1993 - Access 1.1 released to improve compatibility with inclusion the Access Basic programming language. The most significant transition was from Access 97 to Access 2000. 2007 - Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields. Features Users can create tables, queries, forms and reports and connect them together with macros. Option of importing and exporting the data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc. COMP 20213 – DATABASE ADMINISTRATION 8  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH There is also the Jet Database format (MDB or ACCDB in Access 2007), which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments. Microsoft Access offers parameterized queries. These queries and Access tables can be referenced from other programs like VB6 and.NET through DAO or ADO. The desktop editions of Microsoft SQL Server can be used with Access as an alternative to the Jet Database Engine. Microsoft Access is a file server-based database. Unlike the client-server relational database management systems (RDBMS), Microsoft Access does not implement database triggers, stored procedures or transaction logging. SQL Commands The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature − DDL - Data Definition Language Sr.No. Command & Description 1 CREATE Creates a new table, a view of a table, or other object in the database. ALTER 2 Modifies an existing database object, such as a table. DROP 3 Deletes an entire table, a view of a table or other objects in the database. COMP 20213 – DATABASE ADMINISTRATION 9  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH DML - Data Manipulation Language Sr.No. Command & Description 1 SELECT Retrieves certain records from one or more tables. INSERT 2 Creates a record. UPDATE 3 Modifies records. DELETE 4 Deletes records. DCL - Data Control Language Sr.No. Command & Description 1 GRANT Gives a privilege to user. REVOKE 2 Takes back privileges granted from user. COMP 20213 – DATABASE ADMINISTRATION 10  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Data Integrity The following categories of data integrity exist with each RDBMS − Entity Integrity − There are no duplicate rows in a table. Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values. Referential integrity − Rows cannot be deleted, which are used by other records. User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity. Database Normalization Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process − Eliminating redundant data, for example, storing the same data in more than one table. Ensuring data dependencies make sense. Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure. Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form. It is your choice to take it further and go to the fourth normal form, fifth normal form and so on, but in general, the third normal form is more than enough. First Normal Form (1NF) Second Normal Form (2NF) COMP 20213 – DATABASE ADMINISTRATION 11  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Third Normal Form (3NF) SQL - CREATE Database The SQL CREATE DATABASE statement is used to create a new SQL database. Syntax The basic syntax of this CREATE DATABASE statement is as follows − CREATE DATABASE DatabaseName; Example If you want to create a new database , then the CREATE DATABASE statement would be as shown below − CREATE DATABASE testDB; SQL – DROP or DELETE Database The SQL DROP DATABASE statement is used to drop an existing database in SQL schema. Syntax The basic syntax of DROP DATABASE statement is as follows − DROP DATABASE DatabaseName; Always the database name should be unique within the RDBMS. Example If you want to delete an existing database , then the DROP DATABASE statement would be as shown below − DROP DATABASE testDB; NOTE − Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database. COMP 20213 – DATABASE ADMINISTRATION 12  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH SQL – SELECT Database, USE Statement When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed. The SQL USE statement is used to select any existing database in the SQL schema. Syntax The basic syntax of the USE statement is as shown below − USE DatabaseName; Example USE testDB; SQL – CREATE Table Creating a basic table involves naming the table and defining its columns and each column's data type. The SQL CREATE TABLE statement is used to create a new table. Syntax The basic syntax of the CREATE TABLE statement is as follows – CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype,..... columnN datatype, PRIMARY KEY( one or more columns ) ); COMP 20213 – DATABASE ADMINISTRATION 13  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example. Example The following code block is an example, which creates a CUSTOMERS table with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); SQL – DROP or DELETE Table The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table. NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever. COMP 20213 – DATABASE ADMINISTRATION 14  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Syntax The basic syntax of this DROP TABLE statement is as follows − DROP TABLE table_name; Example DROP TABLE CUSTOMERS; SQL – DROP or DELETE Table The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table. ALTER TABLE - ADD Column To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype; The following SQL adds an "Email" column to the "Customers" table: Example ALTER TABLE Customers ADD Email varchar(255); ALTER TABLE - DROP Column To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): COMP 20213 – DATABASE ADMINISTRATION 15  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH ALTER TABLE table_name DROP COLUMN column_name; The following SQL deletes the "Email" column from the "Customers" table: Example ALTER TABLE Customers DROP COLUMN Email; ALTER TABLE – ALTER/MODIFY Column To change the data type of a column in a table, use the following syntax: SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype; Example ALTER TABLE CUSTOMERS ALTER COLUMN age char; SQL Constraints Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table. COMP 20213 – DATABASE ADMINISTRATION 16  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Following are some of the most commonly used constraints available in SQL – NOT NULL Constraint DEFAULT Constraint UNIQUE Constraint PRIMARY Key FOREIGN Key CHECK Constraint NOT NULL Constraint − Ensures that a column cannot have a NULL value. By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such a constraint on this column specifying that NULL is now not allowed for that column. A NULL is not the same as no data, rather, it represents unknown data. Example For example, the following SQL query creates a new table called CUSTOMERS and adds five columns, three of which, are ID NAME and AGE, In this we specify not to accept NULLs – CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); COMP 20213 – DATABASE ADMINISTRATION 17  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH If CUSTOMERS table has already been created, then to add a NOT NULL constraint to the SALARY column in Oracle and MySQL, you would write a query like the one that is shown in the following code block. ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) NOT NULL; DEFAULT Constraint − Provides a default value for a column when none is specified. The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. Example For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, the SALARY column is set to 5000.00 by default, so in case the INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) ); If the CUSTOMERS table has already been created, then to add a DEFAULT constraint to the SALARY column, you would write a query like the one which is shown in the code block below. COMP 20213 – DATABASE ADMINISTRATION 18  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00; Drop Default Constraint To drop a DEFAULT constraint, use the following SQL query. ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT; UNIQUE Constraint − Ensures that all the values in a column are different. The UNIQUE Constraint prevents two records from having identical values in a column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having an identical age. Example For example, the following SQL query creates a new table called CUSTOMERS and adds five columns. Here, the AGE column is set to UNIQUE, so that you cannot have two records with the same age. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); If the CUSTOMERS table has already been created, then to add a UNIQUE constraint to the AGE column. You would write a statement like the query that is given in the code block below. ALTER TABLE CUSTOMERS COMP 20213 – DATABASE ADMINISTRATION 19  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH MODIFY AGE INT NOT NULL UNIQUE; You can also use the following syntax, which supports naming the constraint in multiple columns as well. ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY); DROP a UNIQUE Constraint To drop a UNIQUE constraint, use the following SQL query. ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint; If you are using MySQL, then you can use the following syntax − ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint; PRIMARY Key − Uniquely identifies each row/record in a database table. A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). Note − You would use these concepts while creating database tables. Create Primary Key Here is the syntax to define the ID attribute as a primary key in a CUSTOMERS table. COMP 20213 – DATABASE ADMINISTRATION 20  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); To create a PRIMARY KEY constraint on the "ID" column when the CUSTOMERS table already exists, use the following SQL syntax − ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID); NOTE − If you use the ALTER TABLE statement to add a primary key, the primary key column(s) should have already been declared to not contain NULL values (when the table was first created). For defining a PRIMARY KEY constraint on multiple columns, use the SQL syntax given below. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME) ); To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL syntax. ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME); COMP 20213 – DATABASE ADMINISTRATION 21  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Delete Primary Key You can clear the primary key constraints from the table with the syntax given below. ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; FOREIGN Key − Uniquely identifies a row/record in any another database table. A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table. If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s). Example Consider the structure of the following two tables. CUSTOMERS table CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), COMP 20213 – DATABASE ADMINISTRATION 22  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH PRIMARY KEY (ID) ); ORDERS table CREATE TABLE ORDERS ( ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID) ); If the ORDERS table has already been created and the foreign key has not yet been set, the use the syntax for specifying a foreign key by altering a table. ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID); DROP a FOREIGN KEY Constraint To drop a FOREIGN KEY constraint, use the following SQL syntax. ALTER TABLE ORDERS DROP FOREIGN KEY; CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions. The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered the table. COMP 20213 – DATABASE ADMINISTRATION 23  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH Example For example, the following program creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you cannot have any CUSTOMER who is below 18 years. CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); If the CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement like the one given below. ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE >= 18 ); You can also use the following syntax, which supports naming the constraint in multiple columns as well − ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18); DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL syntax. This syntax does not work with MySQL. ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint; COMP 20213 – DATABASE ADMINISTRATION 24  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH SQL - INSERT Query The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. Syntax There are two basic syntaxes of the INSERT INTO statement which are shown below. INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data. You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax will be as follows − INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); Example The following statements would create six records in the CUSTOMERS table. INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); COMP 20213 – DATABASE ADMINISTRATION 25  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); You can create a record in the CUSTOMERS table by using the second syntax as shown below. INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore', 10000.00 ); ADDITIONAL READING: SQL Data types, Operators and Expressions. https://www.tutorialspoint.com/sql/sql-data-types.htm https://www.tutorialspoint.com/sql/sql-operators.htm https://www.tutorialspoint.com/sql/sql-expressions.htm COMP 20213 – DATABASE ADMINISTRATION 26  Republic of the Philippines POLYTECHNIC UNIVERSITY OF THE PHILIPPINES LOPEZ QUEZON, BRANCH References 1. https://www.tutorialspoint.com/sql/ 2. https://www.w3schools.com/sql/ 3. Modern Database Management, 11th Ed (Ramesh Venkataraman, Jeffrey Hoffer, HeikkiTopi) 4. Database Principles Design, Implementation, & Management Fundamentals (Carlos Coronel, Rob Moris) 5. Database Management System (Yadav, P.K.) COMP 20213 – DATABASE ADMINISTRATION 27