Database

Objective: In this project, we will use DDL statement (CREATE, ALTER, DROP) to create, delete and modify databases and tables inside the database.

Setting up:

  1. Fire up XAMPP Control Panel and start Apache and MySQL module. Once the process start, click the shell on right column.
  2. A new command shell will appear. Type in “mysql -h localhost -u root” and press “Enter” to find the Shell button on the right column.  
  3. MariaDB monitor will open that allows to use connect to MySQL server.
  4. Next type “connect” and press enter. If connection is successful, you are ready to use the MySQL server. Type the SQL statement in command shell followed by semicolon at the end.  

For the above example, we will use our University database -university_db. The database university_db stores data about students, courses and grades in a University as shown below.

  1. STUDENT
  2. COURSE      
  3. DEPARTMENT  

The below steps shows how to modify/delete/create a tables and database step by step using DDL

  1. Create a statement
  2. Create a database

The CREATE statement is used to create databases. The MySQL syntax for creating university_db is :

Use the SHOW statement to find out what databases currently exist on the server. The syntax is:

Once the database is created and verify that the database exists in the server, use USE statement to access and use that database. In this case, we will use the university_db that we created in the step 1.

 Creating Tables:The database we created on above is not filled so now we have to insert data’s on it.

The data above is going to create a table. Now you have to type below command to show tables.

To verify that your table was created the way you want.

ALTER Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Adding column to an existing table is done using ADD statement:

Now, for adding gender

To add a new column on first column, FIRST is used

Deleting the current column now

Modifying and Changing

DROP Statement

The above statement will delete the table where as below statement will delete the database that you created.

RENAME statement

It will rename you table name

DML(DATA MANIPULATION LANGUAGE)

The main goal of this statement is to become familiar with DML statement such as INSERT INTO, SELECT, DELETE and UPDATE. It is used to add, retrieve, modify, delete data’s.

The table of student and its data’s:

Instruction

Instruction in student table as inserting the above data into it.

SELECT Statement
The SELECT statement is used to view the “records” in the table. For example, the statement
below select data from column sid, fname, lname, gender, dept of the student table;

Now, check the output

Sometimes, there will be the case that you may want to select unique values in the column. DISTINCT keyword is used to return only distinct values from the column.

DELETE Statement

The DELETE statement is used to delete any data:=. where as “WHERE” specifies the data you want to delete.

The below statement will delete all the data

UPDATE Statement

The update statement is used to update or modify the data:

And now select the above table

Q1: Retrieve information of student from IT department only.

In addition, SQL support mathematical operator such as <, >, <=, >=, !=, AND, LIKE, BETWEEN and IN which are used with the WHERE clause for choosing particular record of interest. Try entering the following statement to see the operation behavior of the above operator.

3.1. LIKE Operator
The LIKE operator allows WHERE condition to make comparison on only parts of Character. This is also known as pattern matching. The partial character is specified using the character percent ( % ) and underscore ( _ ).

Q2: Retrieve student information whose first name begin in letter ‘p’.

Q3: Retrieve student information whose first name end with letter ‘p’.

Q4. Retrieve student information whose first name contain character string ‘ans’.

Q5. Retrieve student information whose first name have ‘o’ in the third character.

Q6. Retrieve student information whose first name have ‘o’ in the third character and any
character in the remaining.

3.2. ORDER BY
The ORDER BY statement is used to order the records in query results by values of one or more
of the column.
Q7. Retrieve information of all student and order the result by their last name in alphabetical
order.

Q8. Retrieve information of all student and order the result by their last name, and then by first
name in alphabetical order.

Q9. Retrieve information of all student and order the result by their last name in descending
alphabetical order.

3.3. BETWEEN

The comparison operator BETWEEN is used for selecting a records that are within some range of
values.
Q10. Retrieve student information having student ID between 100 and 102.

3.4. IN
The IN operator allows you to specify multiple values from table rows in a WHERE clause.
Q11. Retrieve information of student having first name ‘Hansen’ or ‘Jhon’;