IT 206 Advance Database System: Basic SQL Statements

SQL Overview

• SQL (Structured Query Language) is considered a major reason for the commercial success of relational databases
• SQL statements are used for data definitions, queries, and updates
• There are two main types of SQL statements: DDL (Data Definition Language) and DML (Data Manipulation Language)

MySQL Introduction

• MySQL is a relational database management system (RDBMS) used for developing web-based software applications
• It is fast, reliable, flexible, and easy to use
• MySQL was developed by Michael Widenius and David Axmark in 1994
• Currently, it is developed, distributed, and supported by Oracle Corporation

MySQL Features

• MySQL server design is multi-layered with independent modules
• It is fully multithreaded, using kernel threads, and can handle multiple CPUs
• It provides transactional and non-transactional storage engines
• It has a high-speed thread-based memory allocation system
• It supports in-memory heap tables
• It can handle large databases
• MySQL Server works in client-server or embedded systems
• It works on many different platforms

Basic Data Types

• Numeric data types:
  • Integer numbers: INT, INTEGER, SMALLINT, BIGINT, TINYINT, MEDIUMINT
  • Floating-point (real) numbers: REAL, DOUBLE, FLOAT
  • Fixed-point numbers: DECIMAL(M,[D]), DEC(M,[D]), NUMERIC(M,[D]), NUM(M,[D])
• Character-string data types:
  • Fixed length: CHAR(n), CHARACTER(n)
  • Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n), LONG VARCHAR
• Large object data types:
  • Characters: CLOB, CHAR LARGE OBJECT, CHARACTER LARGE OBJECT
  • Bits: BLOB, BINARY LARGE OBJECT
• Boolean data type:
  • Values of TRUE or FALSE or NULL
• DATE data type:
  • Ten positions, including YEAR, MONTH, and DAY in the form YYYY-MM-DD

CREATE DATABASE Command

• The CREATE DATABASE statement is used to create a new SQL database
• Syntax: CREATE DATABASE [database_name]
• Example: CREATE DATABASE sampledb;

CREATE TABLE Command

• The CREATE TABLE statement is used to create a new table in a database
• Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...)
• Include information for each column, including attribute, domain, key, uniqueness, and null constraints
• Columns can be declared to be NOT NULL
• Columns can be declared to have a default value

ALTER Statement

• The ALTER statement is used to modify the structure of an existing table
• Syntax: ALTER TABLE table_name [ADD|DROP|CHANGE|MODIFY] [column_name] [datatype]
• Example: ALTER TABLE STUDENT ADD TFee int;

INSERT INTO Statement

• The INSERT INTO statement is used to insert new records in a table
• Syntax: INSERT INTO table_name VALUES (ColumnValue1, ColumnValue2, ...)
• Example: INSERT INTO STUDENT VALUES(20191254, 'Castro', 'Macy', 'BSIT', 3);

UPDATE-SET Statement

• The UPDATE statement is used to modify the existing records in a table
• Syntax: UPDATE table_name SET column1 = value1, column2 = value2,...WHERE condition;
• Example: UPDATE STUDENT SET SName = "Reyes", FName = "AJ" WHERE StudNo = '1';

SELECT-FROM Statement

• The SELECT statement is used to select data from a database
• Syntax: SELECT column1, column2,...FROM table_name;
• Example: SELECT StudNo, Sname FROM STUDENT;

DELETE-FROM Statement

• The DELETE statement is used to delete existing records in a table
• Syntax: DELETE FROM table_name WHERE condition;
• Example: DELETE FROM STUDENT WHERE StudNo = 4;

SQL Overview

• SQL (Structured Query Language) is considered a major reason for the commercial success of relational databases
• SQL statements are used for data definitions, queries, and updates
• There are two main types of SQL statements: DDL (Data Definition Language) and DML (Data Manipulation Language)

MySQL Introduction

• MySQL is a relational database management system (RDBMS) used for developing web-based software applications
• It is fast, reliable, flexible, and easy to use
• MySQL was developed by Michael Widenius and David Axmark in 1994
• Currently, it is developed, distributed, and supported by Oracle Corporation

MySQL Features

• MySQL server design is multi-layered with independent modules
• It is fully multithreaded, using kernel threads, and can handle multiple CPUs
• It provides transactional and non-transactional storage engines
• It has a high-speed thread-based memory allocation system
• It supports in-memory heap tables
• It can handle large databases
• MySQL Server works in client-server or embedded systems
• It works on many different platforms

Basic Data Types

• Numeric data types:
  • Integer numbers: INT, INTEGER, SMALLINT, BIGINT, TINYINT, MEDIUMINT
  • Floating-point (real) numbers: REAL, DOUBLE, FLOAT
  • Fixed-point numbers: DECIMAL(M,[D]), DEC(M,[D]), NUMERIC(M,[D]), NUM(M,[D])
• Character-string data types:
  • Fixed length: CHAR(n), CHARACTER(n)
  • Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n), LONG VARCHAR
• Large object data types:
  • Characters: CLOB, CHAR LARGE OBJECT, CHARACTER LARGE OBJECT
  • Bits: BLOB, BINARY LARGE OBJECT
• Boolean data type:
  • Values of TRUE or FALSE or NULL
• DATE data type:
  • Ten positions, including YEAR, MONTH, and DAY in the form YYYY-MM-DD

CREATE DATABASE Command

• The CREATE DATABASE statement is used to create a new SQL database
• Syntax: CREATE DATABASE [database_name]
• Example: CREATE DATABASE sampledb;

CREATE TABLE Command

• The CREATE TABLE statement is used to create a new table in a database
• Syntax: CREATE TABLE table_name (column1 datatype, column2 datatype, ...)
• Include information for each column, including attribute, domain, key, uniqueness, and null constraints
• Columns can be declared to be NOT NULL
• Columns can be declared to have a default value

ALTER Statement

• The ALTER statement is used to modify the structure of an existing table
• Syntax: ALTER TABLE table_name [ADD|DROP|CHANGE|MODIFY] [column_name] [datatype]
• Example: ALTER TABLE STUDENT ADD TFee int;

INSERT INTO Statement

• The INSERT INTO statement is used to insert new records in a table
• Syntax: INSERT INTO table_name VALUES (ColumnValue1, ColumnValue2, ...)
• Example: INSERT INTO STUDENT VALUES(20191254, 'Castro', 'Macy', 'BSIT', 3);

UPDATE-SET Statement

• The UPDATE statement is used to modify the existing records in a table
• Syntax: UPDATE table_name SET column1 = value1, column2 = value2,...WHERE condition;
• Example: UPDATE STUDENT SET SName = "Reyes", FName = "AJ" WHERE StudNo = '1';

SELECT-FROM Statement

• The SELECT statement is used to select data from a database
• Syntax: SELECT column1, column2,...FROM table_name;
• Example: SELECT StudNo, Sname FROM STUDENT;

DELETE-FROM Statement

• The DELETE statement is used to delete existing records in a table
• Syntax: DELETE FROM table_name WHERE condition;
• Example: DELETE FROM STUDENT WHERE StudNo = 4;

This quiz covers the basics of SQL statements, including DDL and DML, syntax notes, and an introduction to MySQL. Learn about data definitions, queries, and updates in relational databases.