Name: Wency S.

Duque
Course&year: BSINTE2B

Database Management System 1

Laboratory Activity
Basic Commands and Operations

Instruction: Please follow the instructions below. Then give the SQL commands and values.
Please use this document to place your answers.

• Login to your MySQL Command Client

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
2. Create the following table in database:db_pie
Tablename: Categories
Fieldname datatype other attributes
CategoryID int
CategoryName varchar(15)

Description varchar(50)
mysql> USE db_pie
Database changed
mysql> Create Table Categories(CategoryID int PRIMARY KEY,
-> CategoryName varchar(15),
-> Description varchar(50));
Query OK, 0 rows affected (0.03 sec)

Tablename: Customers
Fieldname datatype other attributes
CustomerID char(5)
CompanyName varchar(40)
ContactName varchar(30)
ContactTitle varchar(30)
Address varchar(60)
City varchar(15)
Region varchar(24)
mysql> CREATE TABLE Customers(
-> CusromerID char(5) Primary KEY,
-> CompanyName varchar(40),
-> ContactName varchar(30),
 -> ContactTitle varchar(30),
-> Address varchar(60),
-> City varchar(15),
-> Region varchar(24));
Query OK, 0 rows affected (0.03 sec)

Tablename: Employees
Fieldname datatype other attributes
Lastname varchar(20)
Firstname varchar(10)
Title varchar(30)
Birthdate datetime
Hiredate datetime
Address varchar(60)
City varchar(15)
Region varchar(15)
Country varchar(15)
ReportsTo int
mysql> CREATE TABLE Employees(
-> Lastname varchar(20),
-> Firstname varchar(10
-> ),
-> Title varchar(30),
-> Birthdate datetime,
-> Hiredate datetime,
-> Address varchar(60),
-> City varchar(15),
-> Region varchar(15),
-> Country varchar(15),
-> ReportsTo int);
Query OK, 0 rows affected (0.03 sec)

3.Create database name:db_pisoc (note: if database:db_pisoc is not yet existing)

mysql> CREATE DATABASE IF NOT EXISTS db_pisoc;

Query OK, 1 row affected (0.00 sec)
4. Create the following table in database:db_pisoc
Tablename: OrderDetails
Fieldname datatype other attributes
ProductID int
UnitPrice decimal(19, 4)
Quantity smallint
Discount float
mysql> USE db_pisoc;
Database changed
mysql> CREATE TABLE OrderDetails(
-> ProductID int PRIMARY KEY,
-> UnitPrice decimal(19, 4),
-> Quantity smallint,
-> Discount float);
Query OK, 0 rows affected (0.03 sec)
 Tablename: Products
Fieldname datatype other attributes
ProductName varchar(40)
SupplierID int
CategoryID int
QuantityPerUnit varchar(20)
UnitPrice decimal(19, 4)
UnitsInStock smallint
UnitsOnOrder smallint
ReorderLevel smallint

mysql> CREATE TABLE Products(

-> ProductName varchar(40) PRIMARY KEY,
-> SupplierID int,
-> CategoryID int,
-> QuantityPerUnit varchar(20),
-> UnitPrice decimal(19, 4),
-> UnitsInStock smallint,
-> UnitsOnOrder smallint,
-> ReoroderLevel smallint);
Query OK, 0 rows affected (0.03 sec)

Tablename: Shippers
Fieldname datatype other attributes
ShipperID int
ComapnyName varchar(40)
Phone varchar(24)
mysql> CREATE TABLE Shippers(
-> ShipperID int PRIMARY KEY,
-> CompanyName varchar(40),
-> Phone varchar(24));
Query OK, 0 rows affected (0.03 sec)

5. Create the following table in database:db_pie

Tablename: Suppliers
Fieldname datatype other attributes
SupplierID int
CompanyName varchar(40)
ContactName varchar(30)
ContactTitle varchar(30)
Address varchar(60)
City varchar(15)
Region varchar(15)
PostalCode varchar(10)
Country varchar(15)
Phone varchar(15)
Fax varchar(24)
mysql> USE db_pie;
Database changed
mysql> CREATE TABLE Suppliers(
-> SupplierID int PRIMARY KEY,
 -> CompanyName varchar(40),
-> ContactName varchar(30),
-> ContactTitle varchar(30),
-> Address varchar(60),
-> City varchar(15),
-> Region varchar(15),
-> PostalCode varchar(10),
-> Country varchar(15),
-> Phone varchar(15),
-> Fax varchar(24));
Query OK, 0 rows affected (0.03 sec)

6. Create the following table in database: db_pisoc

Tablename: Carrier
Fieldname datatype other attributes
CarrierID int
CarrierName varchar(30)
Address varchar(50)
ContactInfo varchar(30)
ContactNumber varchar(15)
Services varchar(50)
mysql> USE db_pisoc;
Database changed
mysql> CREATE TABLE Carrier(
-> CarrierID int PRIMARY KEY,
-> CarrierName varchar(30),
-> Address varchar(50),
-> ContactInfo varchar(30),
-> ContactNumber varcar(15),
-> Services varchar(50));
Query OK, 0 rows affected (0.03 sec)

Tablename: Branch
Fieldname datatype other attributes
BranchID int
Location varchar(50)
EmployeeID varchar(30)
ContactNumber varchar(15)
mysql> create table Branch(BranchID int,
-> Location varchar(50),
-> EmployeeID varchar(50),
-> ContactNumber varchar(15));
Query OK, 0 rows affected (0.02 sec)

7. Create database name:db_school (note: if database:db_school is not yet existing)

mysql> use db_school;

8. Create the following table in database: db_school

Tablename: Faculty
Fieldname datatype other attributes
FacultyID int
FacultyLastName varchar(25) NOT NULL
FacultyFirstName varchar(25) NOT NULL
 FacultyMiddleName varchar(25) NOT NULL
Department varchar(25)
Position varchar(30)
Degree varchar(30)
Major varchar(25)
Awards varchar(25)
Rank varchar(20)
GrossSalary decimal(19, 4) NOT NULL
Deductions smallint
mysql> create table Faculty(FacultyID int,
-> FacultyLastName varchar(25) NOT NULL,
-> FacultyFirstName varchar(25)NOT NULL,
-> FacultyMiddleName varchar(25)NOT NULL,
-> Department varchar(25),
-> Position varchar(30),
-> Degree varchar(30),
-> Major varchar(25),
-> Awards varchar(25),
-> Rank varchar(20),
-> GrossSalary decimal(19,4)NOT NULL,
-> Deductions int);
Query OK, 0 rows affected (0.01 sec)

9. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands

mysql> ALTER TABLE suppliers

-> ADD COLUMN State varchar(15);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
Database: db_pie
Tablename: Suppliers
Operation: Add the following columns/fields.
Fieldname datatype
State varchar(15)
ContactAddress varchar(25)
mysql> ALTER TABLE suppliers
-> ADD ContactAddress varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SupplierID | int(11) | NO | PRI | NULL | |
| CompanyName | varchar(40) | YES | | NULL | |
| ContactName | varchar(30) | YES | | NULL | |
| ContactTitle | varchar(30) | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| PostalCode | varchar(10) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| Phone | varchar(15) | YES | | NULL | |
| Fax | varchar(24) | YES | | NULL | |
| State | varchar(15) | YES | | NULL | |
| ContactAddress | varchar(25) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
13 rows in set (0.01 sec)

Database: db_pie
Tablename: Suppliers
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
ContactName to ContactName varchar(25)
Fax to FaxNumber varchar(24)
mysql> ALTER TABLE suppliers
-> change ContactName ContactName varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe suppliers;

+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SupplierID | int(11) | NO | PRI | NULL | |
| CompanyName | varchar(40) | YES | | NULL | |
| ContactName | varchar(25) | YES | | NULL | |
| ContactTitle | varchar(30) | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| PostalCode | varchar(10) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| Phone | varchar(15) | YES | | NULL | |
| Fax | varchar(24) | YES | | NULL | |
| State | varchar(15) | YES | | NULL | |
| ContactAddress | varchar(25) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

10.Create the following table in database: db_school

Tablename: Student
Fieldname datatype other attributes
StudentID int
StudentFirstName varchar(25)
StudentMiddleName varchar(25)
StudentLastName varchar(25)
Gender varchar(6)
Age int
Birthdate datetime
CourseID varchar(15)
Year smallint
mysql> use db_school;
Database changed
mysql> Create table Student(StudentID int,
-> StudentFirstName varchar(25),
 -> StudentMiddleName varchar(25),
-> StudentLastName varchar(25),
-> Gender varchar(6),
-> Age int,
-> BirthDate datetime,
-> CourseID varchar(15),
-> Year smallint);
Query OK, 0 rows affected (0.02 sec)

11. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Database: db_school
Tablename: Faculty
Operation: Add the following columns/fields.
Fieldname datatype
Hireddate datetime
mysql> ALTER TABLE faculty
-> ADD HiredDate datetime;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe faculty;

+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| FacultyID | int(11) | YES | | NULL | |
| FacultyLastName | varchar(25) | NO | | NULL | |
| FacultyFirstName | varchar(25) | NO | | NULL | |
| FacultyMiddleName | varchar(25) | NO | | NULL | |
| Department | varchar(25) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Degree | varchar(30) | YES | | NULL | |
| Major | varchar(25) | YES | | NULL | |
| Awards | varchar(25) | YES | | NULL | |
| Rank | varchar(20) | YES | | NULL | |
| GrossSalary | decimal(19,4) | NO | | NULL | |
| Deductions | int(11) | YES | | NULL | |
| HiredDate | datetime | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

12. Create the following tables in database: db_school

Tablename: Subject
Fieldname datatype other attributes
Code int NOT NULL
CourseNumber varchar(25)
DescriptionTitle varchar(50)
Unit smallint
Schedule varchar(30)
Room varchar(20)
LabFee decimal(19, 4)
mysql> CREATE TABLE Subject(Code int NOT NULL,
-> CourseNumber varchar(25),
-> DescriptionTite varchar(50),
-> Unit smallint,
 -> Schedule varchar(30),
-> Room varchar(20),
-> LabFee decimal(19,4));
Query OK, 0 rows affected (0.02 sec)

Tablename: Department
Fieldname datatype other attributes
DepartmentID varchar(15)
Description varchar(50)
mysql> create table Department(DepartmentID varchar(15),
-> Description varchar(50));
Query OK, 0 rows affected (0.00 sec)

13. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Database: db_school
Tablename: Faculty
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
Department to DepartmentID varchar(15)
mysql> use db_school;
Database changed
mysql> ALTER TABLE Faculty
-> change Department DepartmentID varchar(15);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe faculty;

+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| FacultyID | int(11) | YES | | NULL | |
| FacultyLastName | varchar(25) | NO | | NULL | |
| FacultyFirstName | varchar(25) | NO | | NULL | |
| FacultyMiddleName | varchar(25) | NO | | NULL | |
| DepartmentID | varchar(15) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Degree | varchar(30) | YES | | NULL | |
| Major | varchar(25) | YES | | NULL | |
| Awards | varchar(25) | YES | | NULL | |
| Rank | varchar(20) | YES | | NULL | |
| GrossSalary | decimal(19,4) | NO | | NULL | |
| Deductions | int(11) | YES | | NULL | |
| HiredDate | datetime | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

14. Create the following table in database: db_pie

Tablename: tbl_users
Fieldname datatype other attributes
UserID int
Password varchar(50) NOT NULL
 Username varchar(50) NOT NULL
UFirstName varchar(25)
UMiddleInitial varchar(3)
ULastName varchar(25)
GroupID int
mysql> use db_pie;
Database changed
mysql> create table tbl_users(UserID int,
-> Password varchar(50) NOT NULL,
-> Username varchar(50) NOT NULL,
-> UFirstName varchar(25),
-> UMiddleInitial varchar(3),
-> ULastName varchar(25),
-> GroupID int);
Query OK, 0 rows affected (0.01 sec)

15. Create the following table in database: db_pisoc

Tablename: tbl_users
Fieldname datatype other attributes
UserID int
Password varchar(50) NOT NULL
Username varchar(50) NOT NULL
UFirstName varchar(25)
UMiddleInitial varchar(3)
ULastName varchar(25)
GroupID int

mysql> use db_pisoc;

Database changed
mysql> create table tbl_users(UserID int,
-> Password varchar(50) NOT NULL,
-> Username varchar(50) NOT NULL,
-> UFirstName varchar(25),
-> UMiddleName varchar(3),
-> ULastName varchar(25),
-> GroupID int);
Query OK, 0 rows affected (0.00 sec)

16. Create the following table in database: db_school

Tablename: tbl_users
Fieldname datatype other attributes
UserID int
Password varchar(50) NOT NULL
Username varchar(50) NOT NULL
UFirstName varchar(25)
UMiddleInitial varchar(3)
ULastName varchar(25)
GroupID int

mysql> use db_school;

Database changed
mysql> create table tbl_users(UserID int,
-> Password varchar(50) NOT NULL,
 -> Username varchar(50) NOT NULL,
-> UFirstName varchar(25),
-> UMiddleName varchar(3),
-> ULastName varchar(25),
-> GroupID int);
Query OK, 0 rows affected (0.02 sec)

17. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Database: db_pie
Tablename: tbl_users
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
UMiddleInitial to UMiddleName varchar(25)
mysql> use db_pie;
Database changed
mysql> Alter table tbl_users
-> change UMiddleInitial UMiddleName varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe tbl_users;

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| UserID | int(11) | YES | | NULL | |
| Password | varchar(50) | NO | | NULL | |
| Username | varchar(50) | NO | | NULL | |
| UFirstName | varchar(25) | YES | | NULL | |
| UMiddleName | varchar(25) | YES | | NULL | |
| ULastName | varchar(25) | YES | | NULL | |
| GroupID | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Database: db_pisoc
Tablename: tbl_users
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
UMiddleInitial to UMiddleName varchar(25)
mysql> use db_pisoc;
Database changed
mysql> Alter Table tbl_users
-> change UMiddleInitial UMiddleName varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe tbl_users;

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| UserID | int(11) | YES | | NULL | |
| Password | varchar(50) | NO | | NULL | |
| Username | varchar(50) | NO | | NULL | |
| UFirstName | varchar(25) | YES | | NULL | |
| UMiddleName | varchar(25) | YES | | NULL | |
| ULastName | varchar(25) | YES | | NULL | |
| GroupID | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Database: db_school
Tablename: tbl_users
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
UMiddleInitial to UMiddleName varchar(25)
mysql> use db_school;
Database changed
mysql> Alter TABLE tbl_users
-> change UMiddleInitial UMiddleName varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe tbl_users;

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| UserID | int(11) | YES | | NULL | |
| Password | varchar(50) | NO | | NULL | |
| Username | varchar(50) | NO | | NULL | |
| UFirstName | varchar(25) | YES | | NULL | |
| UMiddleName | varchar(25) | YES | | NULL | |
| ULastName | varchar(25) | YES | | NULL | |
| GroupID | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

18. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Database: db_pisoc
Tablename: Carrier
Operation: Add the following columns/fields.
Fieldname datatype
Branches varchar(25)
DateEstablished datetime
mysql> use db_pisoc;
Database changed
mysql> ALTER TABLE Carrier
-> ADD COLUMN Branches varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Carrier

-> ADD COLUMN DateEstablished datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe carrier;

+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| CarrierID | int(11) | NO | PRI | NULL | |
| CarrierName | varchar(30) | YES | | NULL | |
| Address | varchar(50) | YES | | NULL | |
| ContactInfo | varchar(30) | YES | | NULL | |
| ContactNumber | varchar(15) | YES | | NULL | |
| Services | varchar(50) | YES | | NULL | |
| Branches | varchar(25) | YES | | NULL | |
| DateEstablished | datetime | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

Database: db_school
Tablename: Faculty
Operation: Add the following columns/fields.
Fieldname datatype
Sex varchar(6)
Religion varchar(25)
BirthDate Datetime
mysql> ALTER TABLE Faculty
-> ADD COLUMN Sex varchar(6);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Faculty

-> ADD COLUMN Religion varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Faculty

-> ADD COLUMN BirthDate datetime;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe Faculty;

+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| FacultyID | int(11) | YES | | NULL | |
| FacultyLastName | varchar(25) | NO | | NULL | |
| FacultyFirstName | varchar(25) | NO | | NULL | |
| FacultyMiddleName | varchar(25) | NO | | NULL | |
| DepartmentID | varchar(15) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Degree | varchar(30) | YES | | NULL | |
| Major | varchar(25) | YES | | NULL | |
| Awards | varchar(25) | YES | | NULL | |
| Rank | varchar(20) | YES | | NULL | |
| GrossSalary | decimal(19,4) | NO | | NULL | |
| Deductions | int(11) | YES | | NULL | |
| HiredDate | datetime | YES | | NULL | |
| Sex | varchar(6) | YES | | NULL | |
| Religion | varchar(25) | YES | | NULL | |
| BirthDate | datetime | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

19. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Operation: Modify the following tables

Database: db_pie
Tablename Tablename
tbl_users to PIEUsers
mysql> use db_pie;
Database changed
mysql> RENAME TABLE tbl_users TO PIEUsers;
Query OK, 0 rows affected (0.02 sec)

Database: db_school
Tablename Tablename
tbl_users to SchoolUsers
mysql> use db_school;
Database changed
mysql> RENAME TABLE tbl_users TO SchoolUsers;
Query OK, 0 rows affected (0.00 sec)

Database: db_pisoc
Tablename Tablename
tbl_users to PisocUsers
mysql> use db_pisoc;
Database changed
mysql> RENAME TABLE tbl_users TO PisocUsers;
Query OK, 0 rows affected (0.00 sec)

20. Create the following tables in database: db_school

Tablename:Staff
Fieldname datatype other attributes
*the same fieldname and datatypes of database: db_pie table: Employees
mysql> use db_school;
Database changed
mysql> Create table Staff(Lastname varchar(20),
-> Firstname varchar(10),
-> Title varchar(30),
-> Birthdate datetime,
-> Hiredate datetime,
-> Address varchar(60),
-> City varchar(15),
-> Region varchar(15),
-> Country varchar(15),
-> ReportsTo int(11));
Query OK, 0 rows affected (0.02 sec)

21. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
Database: db_school
Tablename: Staff
Operation: Add the following columns/fields.
Fieldname datatype
BloodType varchar(5)
Nationality varchar(25)
ContactNumber varchar(15)
mysql> use db_school;
Database changed

mysql> ALTER TABLE Staff

-> ADD COLUMN BloodType varchar(5);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Staff

-> ADD Nationality varchar(25);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Staff

-> ADD ContactNumber varchar(15);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

Database: db_pisoc
Tablename: Branch
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
EmployeeID to StaffID varchar(8)
mysql> use db_pisoc;
Database changed
mysql> ALTER TABLE Branch
-> Change EmployeeID StaffID varchar(8);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe Branch;

+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| BranchID | int(11) | YES | | NULL | |
| Location | varchar(50) | YES | | NULL | |
| StaffID | varchar(8) | YES | | NULL | |
| ContactNumber | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Database: db_pie
Tablename: Employees
Operation: Add the following columns/fields.
Fieldname datatype
EmployeeID varchar(8)
mysql> use db_pie;
Database changed
mysql> ALTER TABLE Employees
-> ADD EmployeeID varchar(8);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

Database: db_school
Tablename: Staff
Operation: Add the following columns/fields.
Fieldname datatype
StaffID varchar(8)

22. Create the following tables in database: db_pisoc

Tablename:Personnel
Fieldname datatype other attributes
*the same fieldname and datatypes of database: db_school table: Staff
*included all the changes made
mysql> use db_pisoc;
Database changed

mysql> create table Personnel(

-> Lastname varchar(20),
-> Firstname varchar(10),
-> Title varchar(30),
-> Birthdate datetime,
-> Hiredate datetime,
-> Address varchar(60),
-> City varchar(15),
-> Region varchar(15),
-> Country varchar(15),
-> ReportsTo int(11),
-> BloodType varchar(5),
-> Nationality varchar(25),
-> ContactNumber varchar(15),
-> StaffID varchar(8));
Query OK, 0 rows affected (0.00 sec)

23. Create the following tables in database: db_school

Tablename:Courses
Fieldname datatype other attributes
CourseID varchar(10)
Description varchar(100)
LastRevised Datetime
CurriculumNo varchar(10)

mysql> use db_school;

Database changed
mysql> Create table Courses(CourseID varchar(10),
-> Description varchar(100),
-> LastRevised Datetime,
-> CurriculumNo varchar(10));
Query OK, 0 rows affected (0.01 sec)

24. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands

Operation: Modify the following tables

Database: db_pie
Tablename Tablename
Categories tbl_Categories
Customers tbl_Customers
Employees tbl_Employees
Suppliers tbl_Suppliers
PIEUsers tbl_PIEUsers
mysql> use db_pie;
Database changed
mysql> RENAME TABLE Categories TO tbl_categories;
Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Customers TO tbl_Customers;

Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Employees TO tbl_Employees;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE Suppliers TO tbl_Suppliers;

Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE PIEUsers TO tbl_PIEUsers;

Query OK, 0 rows affected (0.01 sec)

Database: db_pisoc
Tablename Tablename
Products tbl_Products
OrderDetails tbl_OrderDetails
 Shippers tbl_Shippers
Carrier tbl_Carrier
Branch tbl_Branch
Personnel tbl_Personnel
PisocUsers tbl_PisocUsers

mysql> use db_pisoc;

Database changed
mysql> RENAME TABLE Products TO tbl_Products
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE OrderDetails TO tbl_OrderDetails;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE Shippers TO tbl_Shippers;

Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE Carrier TO tbl_Carrier;

Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE Branch To tbl_Branch;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE Personnel TO tbl_Personnel;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE PisocUsers TO tbl_PisocUsers;

Query OK, 0 rows affected (0.02 sec)

mysql> show tables;

+--------------------+
| Tables_in_db_pisoc |
+--------------------+
| tbl_branch |
| tbl_carrier |
| tbl_orderdetails |
| tbl_personnel |
| tbl_pisocusers |
| tbl_products |
| tbl_shippers |
+--------------------+
7 rows in set (0.00 sec)

Database: db_school
Tablename Tablename
Faculty tbl_Faculty
Student tbl_Student
 Subject tbl_Subject
Department tbl_Department
Staff tbl_Staff
Courses tbl_Courses
SchoolUsers tbl_SchoolUsers

mysql> use db_schoool;

ERROR 1049 (42000): Unknown database 'db_schoool'
mysql> use db_school;
Database changed
mysql> RENAME TABLE Faculty TO tbl_Faculty;
Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Student TO tbl_Student;

Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Subject TO tbl_Subject;

Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Department TO tbl_Department;

Query OK, 0 rows affected (0.02 sec)

mysql> RENAME TABLE Staff TO tbl_Staff;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE Courses TO tbl_Courses;

Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE SchoolUsers

-> TO tbl_SchoolUsers;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+---------------------+
| Tables_in_db_school |
+---------------------+
| tbl_courses |
| tbl_department |
| tbl_faculty |
| tbl_schoolusers |
| tbl_staff |
| tbl_student |
| tbl_subject |
+---------------------+
7 rows in set (0.00 sec)

25. Create the following:

Databasename: db_pis (note: if database:db_pis is not yet existing)
Tablename: tbl_Baptism
Fieldname datatype other attributes
ID int
mysql> create database db_pis;
ERROR 1007 (HY000): Can't create database 'db_pis'; database exists
mysql> use db_pis;
Database changed
mysql> create table tbl_Baptism(ID int);
Query OK, 0 rows affected (0.01 sec)

Tablename: tbl_Church
Fieldname datatype other attributes
ChurchID int
mysql> create table tbl_Church(ChurchID int);
Query OK, 0 rows affected (0.00 sec)

Tablename: tbl_Activities
Fieldname datatype other attributes
ActivityID int
mysql> create table tbl_Activities(ActivityID int);
Query OK, 0 rows affected (0.01 sec)

26. Modify the following tables using other DDL (e.g. UPDATE, ALTER, DROP) commands
DatabaseName: db_pie
Tablename: tbl_Employees
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
Address to Address varchar(60)
Title to Position varchar(30)
ReportsTo to Supervisor varchar(25)
mysql> use db_pie;
Database changed
mysql> ALTER TABLE tbl_Employees
-> Change Address Address varchar(60);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Employees

-> Change Title Position varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Employees

-> Change ReportsTo Supervisor varchar(25);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

DatabaseName: db_pis
Tablename: tbl_Baptism
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
ID to BaptismID int
mysql> use db_pis;
Database changed
mysql> ALTER TABLE tbl_Baptism
-> Change ID BaptismID int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

DatabaseName: db_pis
Tablename: tbl_Baptism
Operation: Add the following columns/fields.
Fieldname datatype other attributes
FirstName varchar(25)
MiddleName varchar(25)
LastName varchar(25)
Age int
BirthDate Datetime
mysql> ALTER TABLE tbl_Baptism
-> ADD COLUMN FirstName varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Baptism

-> ADD COLUMN MiddleName varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Baptism

-> ADD COLUMN LastName varchar(25);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Baptism

-> ADD COLUMN Age int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Baptism

-> ADD COLUMN BirthDate Datetime;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
DatabaseName: db_pisoc
Tablename: tbl_Products
Operation: Add the following columns/fields.
Fieldname datatype other attributes
Manufacturer varchar(50)
Distributor varchar(50)
Barcode varchar(25)
ExpirationDate DateTime
mysql> use db_pisoc;
Database changed
mysql> ALTER TABLE tbl_Products
-> ADD COLUMN Manufacturer varchar(50);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Products

-> ADD COLUMN Distributor varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Products

-> ADD COLUMN Barcode varchar(25);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Products

-> ADD COLUMN ExpirationDate DateTime;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

DatabaseName: db_pisoc
Tablename: tbl_Shippers
Operation: Modify the following columns/fields.
Fieldname Fieldname Datatype
Phone to ContactNumber varchar(12)
ComapnyName to CompanyName varchar(30)
mysql> use db_pisoc;
Database changed
mysql> ALTER TABLE tbl_Shippers
-> Change Phone ContactNumber varchar(12);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Shippers

-> Change CompanyName CompanyName varchar(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

DatabaseName: db_pis
Tablename: tbl_Activities
Operation: Add the following columns/fields.
Fieldname datatype other attributes
Title varchar(100)
Venue varchar(20)
Schedule DateTime
RegistrationFee decimal(10,2)
Participants varchar(20)
Nature varchar(50)
Remarks varchar(30)
mysql> use db_pis;
Database changed
mysql> ALTER TABLE tbl_Activities
-> ADD COLUMN Title varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN Venue varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN Schedule DateTime;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN RegistrationFee decimal(10,2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN Participants varchar(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN Nature varchar(50);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tbl_Activities

-> ADD COLUMN Remarks varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

27. Identify/Describe all the tables and other attributes(if any) of the following database.
*Note: You can copy your answers from your mysql command line by using copy and paste
operation.

DatabaseName: db_pie
DatabaseName: db_pisoc
DatabaseName: db_school
DatabaseName: db_pis
mysql> use db_pie;
Database changed
mysql> show tables;
+------------------+
| Tables_in_db_pie |
+------------------+
| tbl_categories |
| tbl_customers |
| tbl_employees |
| tbl_pieusers |
| tbl_suppliers |
+------------------+
5 rows in set (0.00 sec)

mysql> describe tbl_categories;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CategoryID | int(11) | NO | PRI | NULL | |
| CategoryName | varchar(15) | YES | | NULL | |
| Description | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> describe tbl_customers;

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CusromerID | char(5) | NO | PRI | NULL | |
| CompanyName | varchar(40) | YES | | NULL | |
| ContactName | varchar(30) | YES | | NULL | |
| ContactTitle | varchar(30) | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(24) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> describe tbl_employees;

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Lastname | varchar(20) | YES | | NULL | |
| Firstname | varchar(10) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Birthdate | datetime | YES | | NULL | |
| Hiredate | datetime | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| Supervisor | varchar(25) | YES | | NULL | |
| EmployeeID | varchar(8) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

mysql> describe tbl_pieusers;

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| UserID | int(11) | YES | | NULL | |
| Password | varchar(50) | NO | | NULL | |
| Username | varchar(50) | NO | | NULL | |
| UFirstName | varchar(25) | YES | | NULL | |
| UMiddleName | varchar(25) | YES | | NULL | |
| ULastName | varchar(25) | YES | | NULL | |
| GroupID | int(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)
mysql> describe tbl_suppliers;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| SupplierID | int(11) | NO | PRI | NULL | |
| CompanyName | varchar(40) | YES | | NULL | |
| ContactName | varchar(25) | YES | | NULL | |
| ContactTitle | varchar(30) | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| PostalCode | varchar(10) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| Phone | varchar(15) | YES | | NULL | |
| Fax | varchar(24) | YES | | NULL | |
| State | varchar(15) | YES | | NULL | |
| ContactAddress | varchar(25) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
13 rows in set (0.02 sec)

28. Identify/Describe all the fieldnames, datatype and other attributes(if any) of the following
database and tables.
*Note: You can copy your answers from your mysql command line by using copy and
paste operation.

DatabaseName: db_pie TableName: tbl_Customers

tbl_Employees
mysql> use db_pie;
Database changed
mysql> describe tbl_customers;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| CusromerID | char(5) | NO | PRI | NULL | |
| CompanyName | varchar(40) | YES | | NULL | |
| ContactName | varchar(30) | YES | | NULL | |
| ContactTitle | varchar(30) | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(24) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> describe tbl_employees;

+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| Lastname | varchar(20) | YES | | NULL | |
| Firstname | varchar(10) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Birthdate | datetime | YES | | NULL | |
| Hiredate | datetime | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| Supervisor | varchar(25) | YES | | NULL | |
| EmployeeID | varchar(8) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

DatabaseName: db_pisoc TableName: tbl_Products

tbl_Personnel
tbl_Shippers
mysql> use db_pisoc;
Database changed

mysql> describe tbl_products;

+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| ProductName | varchar(40) | NO | PRI | NULL | |
| SupplierID | int(11) | YES | | NULL | |
| CategoryID | int(11) | YES | | NULL | |
| QuantityPerUnit | varchar(20) | YES | | NULL | |
| UnitPrice | decimal(19,4) | YES | | NULL | |
| UnitsInStock | smallint(6) | YES | | NULL | |
| UnitsOnOrder | smallint(6) | YES | | NULL | |
| ReoroderLevel | smallint(6) | YES | | NULL | |
| Manufacturer | varchar(50) | YES | | NULL | |
| Distributor | varchar(50) | YES | | NULL | |
| Barcode | varchar(25) | YES | | NULL | |
| ExpirationDate | datetime | YES | | NULL | |
+-----------------+---------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

mysql> describe tbl_personnel;

+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Lastname | varchar(20) | YES | | NULL | |
| Firstname | varchar(10) | YES | | NULL | |
| Title | varchar(30) | YES | | NULL | |
| Birthdate | datetime | YES | | NULL | |
| Hiredate | datetime | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| ReportsTo | int(11) | YES | | NULL | |
| BloodType | varchar(5) | YES | | NULL | |
| Nationality | varchar(25) | YES | | NULL | |
| ContactNumber | varchar(15) | YES | | NULL | |
| StaffID | varchar(8) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
14 rows in set (0.00 sec)
mysql> describe tbl_shippers;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| ShipperID | int(11) | NO | PRI | NULL | |
| CompanyName | varchar(30) | YES | | NULL | |
| ContactNumber | varchar(12) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

DatabaseName: db_school TableName: tbl_Faculty

tbl_Staff
mysql> use db_school;
Database changed
mysql> describe tbl_faculty;
+-------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+-------+
| FacultyID | int(11) | YES | | NULL | |
| FacultyLastName | varchar(25) | NO | | NULL | |
| FacultyFirstName | varchar(25) | NO | | NULL | |
| FacultyMiddleName | varchar(25) | NO | | NULL | |
| DepartmentID | varchar(15) | YES | | NULL | |
| Position | varchar(30) | YES | | NULL | |
| Degree | varchar(30) | YES | | NULL | |
| Major | varchar(25) | YES | | NULL | |
| Awards | varchar(25) | YES | | NULL | |
| Rank | varchar(20) | YES | | NULL | |
| GrossSalary | decimal(19,4) | NO | | NULL | |
| Deductions | int(11) | YES | | NULL | |
| HiredDate | datetime | YES | | NULL | |
| Sex | varchar(6) | YES | | NULL | |
| Religion | varchar(25) | YES | | NULL | |
| BirthDate | datetime | YES | | NULL | |
+-------------------+---------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> describe tbl_staff;

+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| Lastname | varchar(20) | YES | | NULL | |
| Firstname | varchar(10) | YES | | NULL | |
| Title | varchar(30) | YES | | NULL | |
| Birthdate | datetime | YES | | NULL | |
| Hiredate | datetime | YES | | NULL | |
| Address | varchar(60) | YES | | NULL | |
| City | varchar(15) | YES | | NULL | |
| Region | varchar(15) | YES | | NULL | |
| Country | varchar(15) | YES | | NULL | |
| ReportsTo | int(11) | YES | | NULL | |
| BloodType | varchar(5) | YES | | NULL | |
| Nationality | varchar(25) | YES | | NULL | |
| ContactNumber | varchar(15) | YES | | NULL | |
| StaffID | varchar(8) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

DatabaseName: db_pis TableName: tbl_Baptism

tbl_Activities

mysql> use db_pis;

Database changed
mysql> describe tbl_baptism;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| BaptismID | int(11) | YES | | NULL | |
| FirstName | varchar(25) | YES | | NULL | |
| MiddleName | varchar(25) | YES | | NULL | |
| LastName | varchar(25) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
| BirthDate | datetime | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> describe tbl_activities;

+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| ActivityID | int(11) | YES | | NULL | |
| Title | varchar(100) | YES | | NULL | |
| Venue | varchar(20) | YES | | NULL | |
| Schedule | datetime | YES | | NULL | |
| RegistrationFee | decimal(10,2) | YES | | NULL | |
| Participants | varchar(20) | YES | | NULL | |
| Nature | varchar(50) | YES | | NULL | |
| Remarks | varchar(30) | YES | | NULL | |
+-----------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

29. Answer the following questions.

1. How many database do you have on your database?
Answer: 16 databases______
SQL Command: _mysql> show databases;_______

2. How many tables do you have on database: db_pie, db_pisoc, db_school, db_pis,
db_ford
Answers:
DatabaseName No.of Tables
db_pie ???
db_pisoc ???
 db_school ???
db_pis ???
db_ford ???
Answers:
Databases No of Tables
db_pie 5
db_pisoc 7
db_school 7
db_pis 3
db_ford 0 /Empty Set

SQL Commands:
DatabaseName SQL Commands
db_pie ???
db_pisoc ???
db_school ???
db_pis ???
db_ford ???

SQL Commands:
Databases SQLCommands
db_pie mysql> use db_pie;
mysql> show tables;

db_pisoc mysql> use db_pisoc;

mysql> show tables;

db_school mysql> use db_school;

mysql> show tables;

db_pis mysql> use db_pis;

mysql> show tables;

db_ford mysql> use db_ford;

mysql> show tables;

30. Answer the following questions.

1. How many fields do you have on the following database and tables?
Answers:
DatabaseName Tablename No.of Fields
a. db_pisoc tbl_Personnel ???
b. db_pie tbl_Employees ???
c. db_school tbl_Staff ???
d. db_pis tbl_Baptism ???
e. db_pis tbl_Activities ???
f. db_pisoc tbl_Products ???
g. db_school tbl_Subject ???
h. db_pie tbl_Suppliers ???
i. db_pie tbl_Customers ???
j. db_pis tbl_Church ???
SQL Commands:
Databases SQLCommands
db_pie mysql> use db_pie;
mysql> show tables;

db_pisoc mysql> use db_pisoc;

mysql> show tables;

db_school mysql> use db_school;

mysql> show tables;

db_pis mysql> use db_pis;

mysql> show tables;

db_ford mysql> use db_ford;

mysql> show tables;

Commands

a.mysql> use db_pisoc;

mysql> describe tbl_Personnel;

b.mysql> use db_pie;

mysql> describe tbl_Employees;

c.mysql> use db_school;

mysql> describe tbl_Staff;

d.mysql> use db_pis;

mysql> describe tbl_Baptism;

e.mysql> use db_pis;

mysql> describe tbl_Activities;

f.mysql> use db_pisoc;

mysql> describe tbl_Products;

g.mysql> use db_school;

mysql> describe tbl_Subject;

h.mysql> use db_pie;

mysql> describe tbl_Suppliers;

i.mysql> use db_pie;

mysql> describe tbl_Customers;

j.mysql> use db_pis;

mysql> describe tbl_Church;