IT 2105 Mathematics for Computing I Bachelor of
IT 2105 Mathematics for Computing I Bachelor of Information Technology Database Systems I IT 2305 04. Data Manipulation using SQL 4. 2 Creating SQL Databases and Tables 4. 2. 1 Creating a Database 4. 2. 2 Defining tables U J Prashad Srilal BSc (Hons) in Business IT
IT 2305_Database Systems I Intended Learning Outcomes After completing this module students should be able to; • Create a database and tables using SQL. • Understand SQL Syntax.
IT 2305_Database Systems I Outline Creating a Database: CREATE DATABASE, Creating a database schema; Database options: Connect, Disconnect, Select, Close, Create, Drop. Defining tables: CREATE TABLE, ALTER TABLE, DROP TABLE.
IT 2305_Database Systems I CREATE DATABASE statement is used to create a new SQL database Syntax CREATE DATABASE databasename;
IT 2305_Database Systems I CREATE DATABASE test. DB Syntax CREATE DATABASE test. DB; Need to have admin privilege before creating any database
IT 2305_Database Systems I Creating a Database schema • Early versions did not include this concept of a relational database schema, all tables were considered part of the same schema. • This concept is used in SQL 2 to group tables and other constructs that belong to the same database application.
IT 2305_Database Systems I Creating a database schema • A schema is composed of – A schema name – Authorization identifier (To indicate user-account who own the schema) – Schema elements (tables, constraints, views, domains etc…)
IT 2305_Database Systems I Creating a database schema Syntax CREATE SCHEMA COMPANY AUTHORIZATION SMITH;
IT 2305_Database Systems I Database options • Connect • Disconnect • Select • Close • Create • Drop
IT 2305_Database Systems I Drop Database • Use to drop or delete an existing SQL database Syntax DROP DATABASE databasename; Be careful!!! This will result in loss of complete information stored in the database
IT 2305_Database Systems I Drop Database test. DB Syntax DROP DATABASE test. DB;
IT 2305_Database Systems I Question What are the correct syntax/s from the following? (a) DROP DATABASE University (b) CREATE DATABASE University; (c) CREATE SCHEMA Authorization SMITH; (d) CREATE SCHEMA Company AUTHORIZATION SMITH; (e) All of the above
IT 2305_Database Systems I Answer What are the incorrect statement/s from the following? (a) DROP DATABASE University (b) CREATE DATABASE University; (c) CREATE SCHEMA Authorization SMITH; (d) CREATE SCHEMA Company AUTHORIZATION SMITH; (e) All of the above
IT 2305_Database Systems I CREATE TABLE THE CREATE TABLE statement is used to create a new table in a database.
IT 2105 Mathematics for Computing I CREATE TABLE SYNTAX CREATE TABLE _name ( Column 1 datatype , Column 2 datatype , Column 3 datatype , …………. . );
IT 2105 Mathematics for Computing I CREATE TABLE • The column parameters specify the names of the column of the table. • The data type parameter specifies the type of data the column can hold. (e. g. varchar, integer, data, etc. ).
IT 2105 Mathematics for Computing I Data Types Data types might have different names in different database. If the name is same, the size and other details may be different.
IT 2105 Mathematics for Computing I Data Types Numeric – INTEGER, INT Character string – CHAR , VARCHAR Bit string – fixed length Boolean – Logical field values with True or False Timestamp – Date and Time
IT 2105 Mathematics for Computing I Data Types - My. SQL INT(size) –whole numbers Fl. OAT (size) – decimal numbers DATE() – A date. YYYY-MM-DD TIME () – A time. HH: MI: SS
IT 2105 Mathematics for Computing I Data Types - My. SQL CHAR(size) – fixed length string (can store up to 255 characters) VARCHAR(size) – variable length string. (can store up to 255 characters) TEXT – Holds string with a maximum length of 65, 535 characters.
IT 2105 Mathematics for Computing I Data Types – MS Access TEXT – Max 255 characters Memo – Max 65, 536 Byte – Allows whole numbers from 0 -255 Integer – Allows whole numbers between -32, 768 and 32, 767 Currency – can choose which country’s currency to use
IT 2105 Mathematics for Computing I Data Types – MS Access Auto. Number – Automatically give each record its own number, usually starting at 1 Date/Time – Use for date and time Yes/No – A logical field Ole Object – can store pictures, audio, video Hyperlink – Contain links to other fields, including web pages Lookup Wizard – Let you type a list of options(drop – down list
IT 2105 Mathematics for Computing I CREATE TABLE Example The following example create a table called “persons” that contains five columns: Person ID , Last. Name, First. Name, Address, and city
IT 2105 Mathematics for Computing I CREATE TABLE Example CREATE TABLE Persons ( Person ID int (255) Lastname varchar (255) Firstname varchar(255) Address varchar(255) City varchar(255) );
IT 2105 Mathematics for Computing I person. ID Lastname Firstname Address City
IT 2105 Mathematics for Computing I Create Table using Another Table • A copy of an existing table can be create using a combination of the CREATE TABLE statement and the SELECT statement. • The new table gets the same column definition. All column can be selected. • If you create a new table using an existing table, The new table will be filled with the existing values from the old table.
IT 2105 Mathematics for Computing I Create Table using Another Table Syntax CREATE TABLE new_table_name AS SELECT column 1, column 2, , , FROM exisinting_table_name WHERE ………. .
IT 2305_Database Systems I ALTER TABLE Use to ADD, DELETE, or MODIFY columns in an existing table.
IT 2305_Database Systems I ALTER TABLE Syntax ALTER TABLE tablename ADD column_name datatype; ALTER TABLE tablename DROP column_name datatype; ALTER TABLE tablename ALTER COLUMN column_name datatype;
IT 2305_Database Systems I ALTER TABLE EXAMPLE Syntax ALTER TABLE Persons ADD Date. Of. Birth date; ALTER TABLE Persons DROP COLUMN Date. Of. Birth; ALTER TABLE Persons ALTER COLUMN Date. Of. Birth Year;
IT 2305_Database Systems I TRUNCATE Table This statement is used to delete the data inside a table, but not the table itself. Syntax TRUNCATE TABLE table_name;
IT 2305_Database Systems I Drop Table • Use to drop or delete an existing table in a database Syntax DROP TABLE tablename; Be careful!!! This will result in loss of complete information stored in the table
IT 2305_Database Systems I Drop Table Student Syntax DROP TABLE Student;
IT 2305_Database Systems I Question Consider the following data elements. (i) Course name = Database Systems (ii) Course number = IT 2305 (iii) Number of credits = 3 (iv) Compulsory = yes Select from the following, the best possible data type for each data element respectively
IT 2305_Database Systems I Question (a) INTEGER, BOOLEAN, INTEGER, STRING (b) VARCHAR, BYTE, BOOLEAN, INTEGER (c) VARCHAR, INTEGER, BOOLEAN (d) TEXT, CHARACTER, NUMBER, BOOLEAN (e) TEXT, INTEGER, BOOLEAN
IT 2305_Database Systems I Answer (a) INTEGER, BOOLEAN, INTEGER, STRING (b) VARCHAR, BYTE, BOOLEAN, INTEGER (c) VARCHAR, INTEGER, BOOLEAN (d) TEXT, CHARACTER, NUMBER, BOOLEAN (e) TEXT, INTEGER, BOOLEAN
IT 2305_Database Systems I Question Which of the following statements is/are true with respect to data types?
IT 2305_Database Systems I Question (a) VARCHAR data type often reduces disk storage wastage when compared to CHAR data type. (b) BLOB data type can be used to store a photograph or a sound clip. (c) BYTE data type cannot store any type of binary data. (d) Both INTEGER and FLOAT are Numeric data types. (e) INTEGER data type can only store positive integers.
IT 2305_Database Systems I Answer (a) VARCHAR data type often reduces disk storage wastage when compared to CHAR data type. (b) BLOB data type can be used to store a photograph or a sound clip. (c) BYTE data type cannot store any type of binary data. (d) Both INTEGER and FLOAT are Numeric data types. (e) INTEGER data type can only store positive integers.
IT 2305_Database Systems I Lesson Summary Creating a Database: CREATE DATABASE, Creating a database schema; Database options: Connect, Disconnect, Select, Close, Create, Drop. Defining tables: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE.
IT 2105 Mathematics for Computing I Bachelor of Information Technology Database Systems I IT 2305 04. Data Manipulation using SQL 4. 2 Creating SQL Databases and Tables 4. 2. 1 Creating a Database 4. 2. 2 Defining tables U J Prashad Srilal BSc (Hons) in Business IT
- Slides: 41