mis 310 tutorial

8/19/2019 MIS 310 Tutorial

1/31

MIS 310Management Information System

SQL Database TUTORIAL

Mohammed Rashed Uzzaman(RZZ)Faculty, North South UniversityManagement Information System


2/31

MIS 310 Tutorial (RZZ)

2 | P a g e

School of Business

Hello everyone!! Welcome to MIS 310 SQL Tutorial!! In this tutorial we will learn the basics of SQL. We

will start from the installation of local server in your pc.

INSTALLING LOCAL SERVER

We will use “Xampp” in this tutorial,there are also other servers available like “Wamp” and “Mamp”.

“Wamp” is for windows, “Mamp” is for Mac OS. “Xampp” is available for both Windows and Mac OS.

XAMPP is a popular all-in-one kit that installs Apache, MySQL, and PHP in one procedure. XAMPP also

installs phpMyAdmin, a Web application you can use to administer your MySQL databases.

So, let’s first install “Xampp” in your PC. If you don’t have the installation file or if you haven’t collected

it yet, no need to worry, you can download it form http://www.apachefriends.org/en/xampp-

windows.html .

Let’s start the process. First open the folder where you saved your Xampp installation file.

1.

Now, double click the installation file as shown in the Figure 1 and follow the process…

Figure 1: Xampp installation file.


3/31


3 | P a g e

2.

Click the ok button (Figure 2).

Figure 2: Language selection.

3.

Click the next button to start installation as shown in the figure 3.

Figure 3: Start installation.

4.

In this step you have to select a folder from your directory. Its “c:\ xampp” by default. Just click

the “Install” button if you don’t want to install it anywhere else.

Figure 4: Select folder


4/31


4 | P a g e

Figure 5: Installation in progress.

Figure 6: Finish Installation.

5.

Finally click the “Finish” Button to complete the installation process.

Now you’ve successfully installed Xampp server in your pc. Go to your desktop and double click the

Xampp icon to open the Xampp control panel (Figure 7).

Figure 7: Xampp Control Panel.


5/31


5 | P a g e

Figure 7 is a Xampp control panel, from where you have to start Apache and Mysql by pressing the start

buttons. You can start all the services provided by Xampp together, but make sure that you started

Apache and Mysql at least. After starting the services your Xampp server control panel will look like

figure 8.

Figure 8: Start Apache, Mysql.

Ok.. So, finally your computer is ready for Mysql. Now open you web browser and in the address bar

type “localhost” to open your Xampp server.

Figure 9

A page like Figure 10 will appear in the screen where you will be asked to select a language. Select

“English” from the list.

Figure 10: Select Language.


6/31


6 | P a g e

After clicking “English” you will see a page like the one in figure 11.

Figure 11: Select phpMyAdmin.

Click the “phpMyAdmin” link to open phpMyAdmin window. Your phpMyAdmin window will be exactly

like the figure 12.

Figure 12: phpMyAdmin page.

Now we are ready to start our next section where wewill cover basic and some advance features of sql.


7/31


7 | P a g e

SQL

As we are going to use Mysql, it is important to let you know that when you will deal with database and

you have to create, update, select or delete data from your database, you may have to write lines and

lines of sql codes and queries.Mysql’sGUI(Graphical User Interface) made it easy for its user to do all

these complex jobs even without knowing a single sql syntax. But for the advance users it also providessql code typing area. As a student of MIS 310 database course we must learn to write sql queries first.

But don’t worry we will see the use of GUI too.

“SQL is not case sensitive.” So it’s actually doesn’t matter whether yoursql syntax’s are in capital letter

or not. Though professionalsmaintain a standard of capitalizing sql keywords or syntax’s and putting “;”

after finishing a query as a “Good Practice”, it’s not actually required. So you can write a sql query like

you write an English sentence. But it’s important to put the spaces and commas perfectly.

First we will learn how to create a new database.

Creating New Database:

Let’s create our first database. To create a new database we have to use the CREATE DATABASE

statement. So, from your phpMyAdmin click SQL as shown in the Figure 13.

Figure 13: Select SQL.

After clicking SQL a new page like Figure 14 will appear. You will see a blank space where we are


8/31


8 | P a g e

Figure 14: SQL page.

going to write our sql statements. Now type,

CREATE DATABASE `student`;

Again, it’s not important to capitalize the “CREATE DATABASE”statement and putting grave accentmark

(`) before and after of our database name or putting “;” after the statement. But you can follow it if you

want, because it’s a good practice and it reduces the chance of doing mistakes while writing queries.

Don’t mix (``) with quotation mark ( ‘ ’).“ ` ” is used only before and after of database names and table

names. If you put database or table name inside quotation mark, Mysql database will show error and

not going to take your statement. So, make sure that you don’t make these mistakes.

Figure 15: CREATE DATABASE

After typing the statement click “GO” button, right down corner of the window.


9/31


9 | P a g e

Figure 16: GO Button

So, we have created our first database!! Now if you look at the database list which is at the left side of

the window you’ll see the “student” database as shown in the Figure 17.

Figure 17: Select Database from list.

Click and open the student database.

Creating New Table:

Now click and open SQL page again and type:

CREATETABLE`student` (

`id`INTNOTNULLPRIMARY KEY,

`first_name`VARCHAR(100),

`last_name` VARCHAR(100),

`dept`varchar (3),


10/31


10 | P a g e

`address`VARCHAR(255)

);

Here we used CREATE TABLE statement to create a new table named student. You probably have

noticed that this CREATE TABLE statement has two parts. In the first part we mentioned our table name

which is student, and in the second part we wrote the column names that we want to see in our table.

We also have to mention the type and length of the data we are going to insert into these columns. Here

in the “id” column we will insert Integers which is INT in sql. Forfirst_name and last_name, the data type

is VARCHAR which means “Variable Character” and inside the brackets we mentioned the length of the

data (100) which means database will not accept more than 100 characters in this field.You must be

wondering why we have not specified the character length for id; we did that because when we mention

INT as data type, database automatically sets the character length to 11.Last two columns of this table

are department (dept) and address of the student.

It’s really easy to write SQL statement, all you have to do is just to put spacesperfectly. For id we also

specified that, it is NOT NULL, it means this field cannot be left empty when inserting data, otherwise

database is not going to accept data and this is the primary key for this table. PRIMARY KEY is a unique

key for the entries of a table that differentiates contents of each row from each other. Primary Key

cannot be same for multiple entries of a table.

Afterwriting the statement press “GO” button like before. A new table will be created and will be visible

in the left side bar.

Figure 18: Opening Student Table.

Click the student table to open the table. A new page will appear where you will see list of columns you

specified for your table with proper description.


11/31


11 | P a g e

Figure 19: Table Details.

As you can see “id” is the primary key of this table which we nominated while we were creating the

table. Our statement was:

ìd` INT NOT NULL PRIMARY KEY,

It is also possible to write the statement like below:

CREATE TABLE `student` (

ìd` INT NOT NULL,

`first_name` VARCHAR(100),


`dept` varchar (3),

àddress` VARCHAR(255),

primary key(ìd`)

);

We can also add the auto increment feature to our id field. If we want to add auto increment, then we

just have to add “auto_increment” in the statement where we specified our “id” field, like below:

ìd` NOT NULL auto_increment,


12/31


12 | P a g e

Inserting Data Into Tables:

Now we are going to insert data into our student table. To insert data we have to use “INSERT INTO”

statement. Data insertion can be done in two ways. We will see both of them with an example. Let’s

insert a student’s data in our table.

Let’s say, we want to insert a student’s information in our student table who’s Name is Faisal Jahan,

Department: BBA, and Address: 41 Rampura, Dhaka.

Now, go to the sql page again and type:

INSERT INTO `student` (id, first_name, last_name, dept, address)

VALUES ('', 'Faisal', 'Jahan', 'BBA', '41 Rampura, Dhaka');

Click “GO” button.

Figure 20: Insert Data.

As you can see, in this SQL statement we first typed the statement we want to execute which is “INSERT

INTO”, then typed our table name “student” and after that we typed all the attributes/fields/column

names of our table inside bracket and finally typed the values we want to insert into our table using the

“VALUES”syntax. After the VALUES syntax we specified all the data, separated by comma and order by

the column names that we specified before. You must remember, when you’re writing a VARCHAR

element or text you always have to write it inside single quotation marks. But it’s not important when

you are inserting INT data. In this table our “id” field is auto increment enabled. That’s why we don’t

have to write the “id” but you have to leave empty quotation marks.

This is a form of writing INSERT INTO statement. This form is very useful when you want to insert data

into specific columns. All you have to do is just to specify the column name/names where you want to

insert data. But if you want to insert data into all the columns, then you can just write,

INSERT INTO `student` VALUES ('', 'Faisal', 'Jahan', 'BBA', '41 Rampura, Dhaka');

Inserting Multiple rows:

Go to SQL page and write:

INSERT INTO `student`


13/31


13 | P a g e

VALUES ('', 'Tanveer', 'Khan', 'EEE', '11 Bashundhara, Dhaka'),

('', 'Anik', 'Hasan', 'BBA', '63/1 Uttara, Dhaka'),

('', 'Nazmul', 'Islam', 'BBA', '30 Gulshan, Dhaka'),

('', 'Shajib', 'Khan', 'CSE', '21 Dhanmondi, Dhaka');

Press “GO” button to insert data.

As you can see, to insert multiple data at a time you have to put each row’s data inside brackets and

have to separate those using commas.

Now, to see all your inserted data click student from the left side bar.

Figure 21: Opening Student table.

Deleting Database:

To delete a database go to SQL and write

DROP DATABASE `database name`;

But by default the DROP DATABASE function is disabled in Xampp. So if you want to drop your database

then you have to follow the process below:

1.

First go to http://localhost/phpmyadmin/ or click 127.0.0.1 from top.


14/31


14 | P a g e

Figure 22: Opening phpMyadmin Home page.

2.

Click Database.

Figure 23: Opening database list.

3.

Select your database name from the l ist and click drop button.


15/31


15 | P a g e

Figure 24: Select and Drop Database.

Deleting Tables:

To delete a table from your database first go to the SQL page and write

DROP TABLE `table name`;

The table will be deleted.

Queries:

Query is the most important part of sql. Till now we learnt how to create database, table using CREATE

DATABASE and CREATE TABLE statement, learnt how to insert data into table using INSERT INTO

statement and how to delete database and table using DROP DATABASE and DROP TABLE statement.

Now we will learn the use of SELECT statement.

Let’s go back to our student table. Go to the SQL page. Let’s say you want see all the data from your

student table. Write the query below:

SELECT*FROM ̀ student`;


16/31


16 | P a g e

You’ll get all the data you have in your student table.

Figure 25: Query Result.

In short, SELECT* means all. So, here in this query we wanted to see all from the table student.

Now if you want to see particular column/columns from the database then all you have to do is to write

the column name/names after SELECT. In case of multiple columns you have to put commas to separate

them. Let’s see an example,

See all first names from the table. Go to SQL and type:

SELECT `first_name` FROM `student`;


Image above shows how the result is going to look. Let’s see multiple columns. Go to SQL and write the

query below:

SELECT `first_name`, `last_name`, `dept` FROM `student`;


17/31


17 | P a g e


Click the “GO” button to see the result.

SQL WHERE

Most of the time we want to find a particular record from our database. For example, you may want to

find details of a particular student name. To run this query you have to write

SELECT `first_name`, `last_name`, `dept`, `address` FROM `student` WHERE `first_name` = ‘Faisal’;

Image below shows the result we are going to get.


SQL Operators

In the last query we used an operator (‘=’) to find our required value. There is also some other operators

avileable for constracting a query. A list of operators are given below.

Operators Definition

= Equal to the specified value.

!= Not equal to the specified value.

Also a Not equal operator.


18/31


18 | P a g e

> Greater than.

< Less than.

>= Greater than or equal


19/31


20/31


20 | P a g e

SELECT * FROM `student` WHERE NOT `last_name` = ‘Islam’;


Figure 32: Logical operator NOT.

As you can see, query returned all the rows without the row that contains Islam as lastname.

You can also construct complex queries combining multiple logical operators. Practice complex queriesyourself.

ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending

order. Mysql sorts query results in ascending order by default.

Write the query in your SQL page,

SELECT `first_name`, `last_name`, `dept` FROM `student` ORDER BY `first_name`;

Now, click “GO” button to execute the query.

Figure 33: ORDER BY


21/31


21 | P a g e

So, first_name is in ascending order and rest of the columns is being ordered accordingly. You can also

try multiple columns to order your result. You can write “ORDER BY `first_name`, ‘last_name’ if you

want your result to be ordered by both first name and last name.

Comparison Keyword

LIKE, BETWEEN….AND, IN, IS NULL are called comparison keywords in sql.These operators help to

enhance the search capabilities of sql.

LIKE

The LIKE operator is used to return all rows in a table whose column values match the specified pattern.

LIKE is very useful when do not know the entire value. We have to use wildcard character '%' to get our

desired result.

Now, Go to SQL page and write,

SELECT * FROM `student` where `first_name` LIKE ‘Sh%’;


This query will show all the rows whose first name starts with “Sh” and “%” means rest of the letters in

the name can be any character. As you can see we have only one first name that starts with “Sh”, that’s

why the query returned the row of “Shajib”.

Figure 34: LIKE operator.

There is another wildcard character which also used with LIKE. “_” Underscore is used to signify a single

character with LIKE.

BETWEEN…AND

BETWEEN…AND operator is used to compare data for a specified range of values.

For example, we want to see student details whose id’s are between 2 to 4. Let’s write the query in SQL

page

SELECT * FROM `student` WHERE `id` BETWEEN 2 AND 4;

Click “GO” to see the result.


22/31


22 | P a g e

Figure 35: BETWEEN…AND.

IN

The IN operator is used when we want to compare a column with more than one value. It is similar to

OR condition.

Let’s say we want to see the students who is studying eitherBBA or CSE. Write the query in SQL,

SELECT `first_name`, `last_name`, `dept` FROM `student` WHERE `dept` IN(‘BBA’, ‘CSE’);


Result shows the list of students who studies BBA or CSE (Figure 47).

Figure 36: IN Operator.

IS NULL

IS NULL shows the rows for columns that don’t have any values. For example, if we want to find student

who don’t have a last name then the query will be

SELECT `id`, `first_name`, `dept`FROM s̀tudent` WHERE `last_name` IS NULL;


Our query returned an empty set, because we do not have any empty rows or columns. You can try this

query after inserting some new rows with empty values.


23/31


23 | P a g e

Let’s say 3 of the students of student table gets a certain amount of financial aid from University each

month. Let’s create a new table named “aid” to show the amounts. Write the query below to create

“aid” table

CREATE TABLE àid` (

ìd` INT NOT NULL AUTO_INCREMENT,



àmount` INT,

PRIMARY KEY(ìd`)

);

Now, click “GO” to create new table.

Figure 37: Creating table àid`.

Click àid ` to open the table.

As you can see our àid` table is empty. So, first we have to inset data into it. Go to SQL page and write,


24/31


24 | P a g e

INSERT INTO àid` VALUES (‘’, 'Tanveer', 'Khan', 9000),

('', 'Anik', 'Hasan',6000),

('', 'Nazmul', 'Islam', 8000),

('', 'Faisal', 'Jahan', 5600),

Click “GO” to insert data. Now go to the aid table.

Figure 38: ̀ aid` Table.

Figure 39 shows all the information of aid table we just inserted. But we were suppose to insert 3

students in our aid table, we mistakenly added “Faisal” in this list. So, we have to delete this entry.

DELETE

DELETE statement deletes specified information from a specified table. Let’s delete Faisal’s information

from aid table. Go to SQL and write,

DELETE FROM àid` WHERE `first_name` = `Faisal`;

Click “GO” button to delete the row.

Figure 39: Deleting data from àid`.

We deleted the data we inserted mistakenly. Now if you go to aid table you’ll find only three names

listed.

ALTER TABLE


25/31


25 | P a g e

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the

definition of its columns. Simply,its main functions are Add, drop, modify table columns, adding and

dropping primary key. Though we are not going to use ALTER TABLE statement in our database, we will see

how to write ALTER TABLE statements.

Adding a new column:ALTER TABLE `tablename` ADD `columnname` datatype;

Here datatype means the type of data we are going to insert in this column (INT, VARCHAR etc.). If your

data type is VARCHAR then you have to set the character length inside brackets, which we have learnt

before.

Dropping column from table:ALTER TABLE `tablename` DROP `columnname`;

This statement above will drop the specified column from you specified table.

Changing Datatype of column:ALTER TABLE `table name` ALTER COLUMN `column name` DATATYPE;

This statement is used to change the datatype of a column.

Default:

In short, DEFAULT statement shows a default value if on data is inserted under a particular column. Let’s go

back to our student table to get a better understanding about it. In student table our 4 th column is dept,

which shows the departments of each student. Now, you want that dept column will show BBA for each

student if student’s department is not specified while inserting data into the dept column. DEFAULT can be

specified when you are creating a new table or you can use the ALTER TABLE statement to spacify the

default value for a column.

You could either write:

CREATE TABLE `student` (

`id` INT NOT NULL PRIMARY KEY,



`dept` varchar (3) DEFAULT ‘BBA’,

`address` VARCHAR(255)

);

or we can write:

ALTER TABLE `student`

ALTER `dept` SET DEFAULT 'BBA';

UPDATE


26/31


26 | P a g e

UPDATE statement is used to update, edit or modify data from table. For example, in our àid` table we

inserted a wrong amount in the 3rd

row. We inserted 8000, while our actual amount is 11000.

Figure 40: àid` table before updating data.

So, to correct this we have to write the query below:

UPDATE àid` SET àmount` = 11000 where ìd` = 3;

Click “GO” to run. Click àid` to open the table.

Figure 41:àid` tableafter correcting data.

SQL JOINS

SQL Joins are used to relate information in different tables.A brief explanation of different types of joins

are given below:

JOIN: Return rows when there is at least one match in both tables

INNER JOIN:Returns rows when there is at least one match in both tables.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

FULL JOIN: Return rows when there is a match in one of the tables


27/31


27 | P a g e

We will use our student and aid tables to see examples of Join.

JOIN, INNER JOIN

As you can see join and inner join produce same result, that’s why we are going to see example of

joinand inner join together. Type the query below:

SELECT * FROM `student` JOIN àid`;

or

SELECT * FROM `student` INNER JOIN àid`;

Click “GO” to run the query.

As you can see in Figure 43, our query returned 15 rows. This result do not express any meaningful

information. We got this result because we haven’t specified a common column between these two

tables (It can be either first_name or last_name, because these columns are same in both the tables).

Figure 42: JOIN and INNER JOIN Query (Common field is not specified).

But if we write the query correctly then the query becomes

SELECT * FROM `student` JOIN àid` ON (`student`.`first_name` = àid`.`first_name`);

or

SELECT * FROM `student` INNER JOIN àid` ON (`student`.`first_name` = àid`.`first_name`);

Click “GO”.


28/31


28 | P a g e

Figure 43: Result of JOIN, INNER JOIN Query.

This time query returns 3 rows, which fulfills the condition (`student`.`first_name` = àid`.`first_name`).

You must be wondering, what is this `student`.`first_name`? After “ON” we have to set the column

names from both the tables which contain identical values. But if the column names are same then

database shows error message. That’s why we have to put the table name before the column names

joined by a dot “.”. So, `student`.`first_name` means first_name is a column of student table. But you

can write only the column names if your column names are not same.

LEFT JOIN

Left join returnsall the values from the left table, even if there is no match found in the right table.

Write the query below:

SELECT * FROM `student` LEFT JOIN àid` ON ( `student`.`first_name` = àid`.`first_name` );

Click “GO” to see the result.

Figure 44: LEFT JOIN

RIGHT JOIN

Right join returnsall the values from the right table, again, even if there is no match found in the left

table.

Write down the query:

SELECT * FROM `student` RIGHT JOIN àid` ON ( `student`.`first_name` = àid`.`first_name` );

Click “GO”.


29/31


29 | P a g e

Figure 45: RIGHT JOIN

We got this result because all the data of right table is available in the left table too.

SQL FUNCTIONS

SQL functions are built-in SQL functions that work on groups of rows and return one value for the entire

group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

COUNT()

COUNT()function returns the number of rows in the table that satisfies the condition specified in the

WHERE condition. If the WHERE condition is not specified, then the query returns the total number of

rows in the table.

Write the query below in you sql page:

SELECT COUNT(`last_name`) FROM `student`;

Click “GO” for see result.

Figure 46: No. of rows in last_name column

MAX()

This function returns the maximum value from specified column. Write the query below.

SELECT MAX(`amount`) FROM `aid`;

Click “GO”.


30/31


30 | P a g e

Figure 47: Maximum value of column amount.

MIN()

MIN() function returns the minimum value from specified column. Write this query

SELECT MIN(àmount`) FROM àid`;

Click “GO”.

Figure 48: Minimum value of column amount.

AVG()

This function calculates the average value from specified column. Type this query in sql,

SELECT AVG(àmount`) FROM àid`;

Click “GO” to run the query.

Figure 49: Average value of column amount.

SUM()

I’m sure you already understood what this function does. It returns the sum of a numeric column. Type

the query below,

SELECT SUM(àmount`) FROM àid`;

Cilck “GO”


31/31


DISTICNT

In our student table dept column contains value ‘BBA’ in multiple rows. Likewise columns may containduplicate values. Sometimes you will want to list only the different (distinct) values in a table. TheDISTINCT keyword can be used to return only distinct (different) values.

Let’s see the distinct values of dept column:

SELECT DISTINCT ̀ dept` FROM ̀ student`;


mis 310 tutorial

Documents