Introduction to SQL and Data Definition Language PDF

Introduction to Structured Query Language (SQL) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 1  Learning Objectives ...

Introduction to Structured Query Language (SQL) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 1  Learning Objectives  In this chapter, you will learn:  What is SQL  The basic commands and functions of SQL  How to use SQL for data administration (to create tables and indexes)  How to use SQL for data manipulation (to add, modify, delete, and retrieve data)  How to use SQL to query a database for useful information ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 2  SQL Overview  Structured Query Language  The standard for relational database management systems (RDBMS)  RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  History of SQL  1970s–Edgar F. Codd develops relational database concept.  System R with Sequel (later SQL) created at IBM Research Lab by Donald D. Chamberlin and Raymond F. Boyce  1979–Oracle markets first relational DB with SQL  1981 – SQL/DS first available RDBMS system on DOS/VSE  Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986)  1986–American National Standards Institute (ANSI) SQL standard released  1989, 1992, 1999, 2003, 2006, 2008,2011, 2016–Major ANSI standard updates  Current–SQL is supported by most major database vendors ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Purpose of SQL Standard  Specify syntax/semantics for data definition and manipulation  Define data structures and basic operations  Enable portability of database definition and application modules  Specify minimal (level 1) and complete (level 2) standards  Allow for later growth/enhancement to standard (referential integrity, transaction management, user- defined functions, extended join operations, national character sets) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Benefits of a Standardized Relational Language  Reduced training costs  Productivity  Application portability  Application longevity  Reduced dependence on a single vendor  Cross-system communication ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  SQL Environment  Catalog  A set of schemas that constitute the description of a database  Schema (Database)  The structure that contains descriptions of objects created by a user (base tables, views, constraints)  SQL Commands ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Structured Query Language (SQL)  Categories of SQL Language  Data Definition Language (DDL)  Data Manipulation Language (DML)  Data Control Language (DCL)  Transaction Control Language (TCL)  Nonprocedural language with basic command vocabulary set of less than 100 words  Differences in SQL dialects are minor ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 8  Common SQL Commands Language Command List CREATE DROP DDL ALTER RENAME TRUNCATE SELECT INSERT DML UPDATE DELETE GRANT DCL REVOKE COMMIT TCL ROLLBACK ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  The Database Model ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 10 Tasks to be Completed Before Using a New RDBMS  Create database structure  RDBMS creates physical files that will hold database  Differs from one RDBMS to another  Authentication: Process DBMS uses to verify that only registered users access the data  Required for the creation tables  User should log on to RDBMS using user ID and password created by database administrator ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 11  SQL Data Types - Numeric Data Type Syntax Description INT A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits. TINYINT A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits. SMALLINT A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits. MEDIUMINT A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits. BIGINT A large integer that can be signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  SQL Data Types - Numeric FLOAT(L,D) A floating-point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a float. DOUBLE(L,D) A double precision floating-point number that cannot be unsigned. You can define the display length (m) and the number of decimals (d). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a double. Real is a synonym for double. DECIMAL(L,D) An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte. Defining the display length (m) and the number of decimals (d) is required. Numeric is a synonym for decimal. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  SQL Data Types – Date Time Data Type Syntax Maximum Size Explanation DATE Values range from '1000-01-01' to Displayed as 'yyyy-mm-dd'. '9999-12-31'. DATETIME Values range from '1000-01-01 Displayed as 'yyyy-mm-dd 00:00:00' to '9999-12-31 23:59:59'. hh:mm:ss'. TIMESTAMP(m) Values range from '1970-01-01 Displayed as 'YYYY-MM- 00:00:01' UTC to '2038-01-19 DD HH:MM:SS'. 03:14:07' TC. TIME Values range from '-838:59:59' to Displayed as 'HH:MM:SS'. '838:59:59'. YEAR[(2|4)] Year value as 2 digits or 4 digits. Default is 4 digits. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  SQL Data Types – String Data Type Syntax Maximum Size Explanation CHAR(size) 255 characters Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters. VARCHAR(size) 255 characters Where size is the number of characters to store. Variable- length string. TINYTEXT(size) 255 characters Where size is the number of characters to store. TEXT(size) 65,535 characters Where size is the number of characters to store. MEDIUMTEXT(si 16,777,215 Where size is the number of characters to store. ze) characters LONGTEXT(size) 4GB or Where size is the number of characters to store. 4,294,967,295 characters BINARY(size) 255 characters Where size is the number of binary characters to store. Fixed-length strings. Space padded on right to equal size characters.(introduced in MySQL 4.1.2) VARBINARY(size) 255 characters Where size is the number of characters to store. Variable- length string.(introduced in MySQL 4.1.2) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. SQL Data Types – Binary Large Object Data Type Syntax Maximum Size TINYBLOB Maximum size of 255 bytes. BLOB(size) Maximum size of 65,535 bytes. MEDIUMBLOB Maximum size of 16,777,215 bytes. LONGTEXT Maximum size of 4gb or 4,294,967,295 characters. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Installing MySQL  You can install MySQL as a standalone package  Or, install XAMPP, a software suite, which includes:  Apache, MariaDB(SQL), PHP, Pearl ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  XAMPP Interface ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Testing MySQL Connection ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Testing MySQL Connection  -h (means host)  -u (means username) ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Creating the Database  Logical group of database objects related to each other  Command  CREATE DATABASE database_name;  Example:  CREATE DATABASE mydb; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 21  Display Databases  You can check the created database by the following query:  SHOW DATABASES; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Selecting a Database  In order to work with a database, it must be selected first.  USE database_name;  Example:  USE mydb; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Deleting a Database  You can drop/delete/remove a MySQL database easily with the MySQL command. You should be careful while deleting any database because you will lose your all the data available in your database.  DROP DATABASE database_name;  Example:  DROP DATABASE mydb; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Creating Table Structures  The MySQL CREATE TABLE command is used to create a new table into the database. A table creation command requires three things:  Name of the table  Names of fields  Definitions for each field ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Creating Table Structures  Use one line per column (attribute) definition  Use spaces to line up attribute characteristics and constraints  Table and attribute names are capitalized  Features of table creating command sequence  NOT NULL specification These are called constraints  UNIQUE specification ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 26  Creating Table Structures  SQL constraints are used to specify rules for the data in a table.  Constraints 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 table. If there is any violation between the constraint and the data action, the action is aborted.  More constraints are to be discussed in the next chapters. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Creating Table Structures  CREATE TABLE table_name ( column1 datatype other_constraints, column2 datatype other_constraints, column3 datatype other_constraints,.... ); ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Seatwork  Write the syntax to create a table called students with the following columns(attributes):  ID number  Last Name  First Name Identify the necessary data type, length of data and  Middle Name other constraints  Address  Age  Contact Number ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Sample Answer #1 ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Sample Answer #2 ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Review Table Structure  Use the following command to see tables already created:  DESCRIBE table_name;  Example:  DESCRIBE students; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Changing Table Structure  MySQL ALTER statement is used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.  The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the situation. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Changing Table Structure  ADD a column in the table  ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];  table_name: It specifies the name of the table that you want to modify.  new_column_name: It specifies the name of the new column that you want to add to the table.  column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).  FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Seatwork  Given the table:  Add a new column/attribute email before address.  Add a new column course at the end of the table.  Identify the necessary data type, length of data and other constraints ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Sample Answer ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Changing Table Structure  The MODIFY command is used to change the column definition of the table.  ALTER TABLE table_name MODIFY column_name column_definition [ FIRST | AFTER column_name ]; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Seatwork  Given the table:  Modify ContactNumber to not allow null values.  Modify address to be able to store up to 50 characters. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Sample Answer ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Changing Table Structure  To RENAME a column in a table:  ALTER TABLE table_name CHANGE COLUMN old_name new_name column_definition [ FIRST | AFTER column_name ] ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Seatwork  Given the table:  Rename idNumber to StudentNumber.  Rename Email to EmailAdd and swap rows with Address. ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Sample Answer ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Changing Table Structure  To RENAME a table:  ALTER TABLE table_name RENAME TO new_table_name;  Example:  ALTER TABLE students RENAME TO ccestudents; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Deleting a Table Structure  DROP table statement removes the complete data with structure.  DROP TABLE table_name;  Example:  DROP TABLE students; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.  Empty a Table Structure  TRUNCATE statement removes the complete data without removing its structure.  TRUNCATE TABLE table_name;  Example:  TRUNCATE TABLE students; ©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.