SQL/MySQL Concepts Report for Database Management System (MIS 401)

This report, submitted by Shamima Iqbal to Dr. M. Helal Uddin Ahmed at the University of Dhaka, provides an overview of Structured Query Language (SQL) and its application in database management systems. The report begins with an executive summary, highlighting the importance of SQL in modern businesses and the demand for SQL developers. It then defines key concepts such as SQL, RDBMS, tables, fields, records, and columns, illustrating these with examples. The report covers SQL syntax, including essential commands like SELECT, INSERT, UPDATE, and DELETE, along with their general forms and examples. It also explains the WHERE clause, the INSERT INTO statement, the UPDATE statement, the DELETE statement and the ORDER BY clause. Furthermore, the report explores the SELECT TOP statement. The report's structure follows the order of material presented in the W3schools MySQL tutorial and the course lectures, making it a valuable resource for understanding SQL basics and its practical applications.

MIS - 401: Database Management System
Assignment on: SQL/MY SQL
Submitted To:
Dr. M. Helal Uddin Ahmed
Professor
Department of Management Information Systems
University Of Dhaka
Submitted By:
Shamima Iqbal
029-12-078
Section- B
Batch- 12
Department of Management Information Systems
University Of Dhaka
June 1, 2020
1 | P a g e

Dr. M. Helal Uddin Ahmed
Professor
Department of MIS
University of Dhaka
Subject: Submission of the report titled as ‘Structured Query Language’
Sir,
With Due respect and honor submit the report prepared for the course “Database
Management”. It reflects the concept of structured query language (SQL). I tried
to accommodate as much information and relative issues as possible. I would like
to thank you for providing me with this opportunity.
Sincerely yours
Shamima Iqbal
ID: 029-12-078
BBA 12th Batch, Section: B
Department of Management Information Systems
Faculty of Business Studies
University of Dhaka
Executive summary:
2 | P a g e

Now-a-days, almost all businesses, from small online stores to Multinational
corporations use data to run their operations. They manage this data using
databases. Because of this, the demand for database administration experts has
exploded, and because of this demand, working as a database developer,
particularly an SQL developer can be very lucrative. This report shows a brief
summary of the some of the concepts and techniques that we’ve learned from the
W3schools MySQL tutorial and MySQL in the first few lectures given by our
course instructor. It is arranged in order by when the material is presented. It’s a
good learning that we’ve done and what we will do in the next day. We’ve learned
a basic tutorial from that web-based learning platform. We can develop our skills
in SQL/MySQL programming languages. It also contains several business
applications and their optimum solution by using MySQL programming syntax.
What Is SQL:
3 | P a g e

SQL is used to perform multiple functions in a database, such as running queries,
updating and modifying records, creating views, and setting permissions. Many
significant commands are used in SQL, such as select, alter database, drop table,
drop index, update, delete, create table, create database, create index, insert into,
alter table, etc. All Relational Database Management Systems (RDMS) such as
MySQL, Sybase, MS Access, Informix, and SQL Server use SQL as their standard
language for databases. It can be used to accomplish almost everything that one
needs to do with a database.
What Is RDBMS
RDBMS stands for Relational Database Management System. RDBMS is the basis
for SQL, and for all modern database systems like MS SQL Server, Oracle, IBM
DB2, MySQL, and Microsoft Access. A Relational database management system
is a database management system that is based on the relational model.
What is Table?
The data in an RDBMS is stored in database objects which are called as tables.
This table is basically a collection of related data entries and it consists of
numerous columns and rows. Remember, a table is the most common and simplest
form of data storage in a relational database. The following program is an example
of a CUSTOMERS table
ID Name AGE Address Salary
1 Shamima 22 Dhaka 5000
2 Anik 23 Khulna 20000
4 | P a g e

3 Rafsan 23 Rajshahi 10000
4 Dia 22 Dhaka 7000
5 Arfana 25 Narayanganj 12000
6 Mahin 26 Cumilla 8000
7 Tasnim 28 Tangail 25000
What is a field?
Every table is broken up into smaller entities called fields. The fields in the
CUSTOMERS consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a
column in a table that is designed to maintain specific information about every
record in the table.
What is a Record or a Row?
A record is also called as a row of data is each individual entry that exists in a
table. For example, there are 7 record in the above CUSTOMERS table. Following
is a single row of data or record in the CUSTOMERS table –
1 Shamima 22 Dhaka 5000
What is a column?
A column is a vertical entity in a table that contains all information associated
with a specific field in a table. For example, a column in the CUSTOMERS table is
ADDRESS, which represents location description and would be as shown below –
ID
1
2
3
4
5
5 | P a g e

6
7
SQL Syntax
The syntax of a language describes the language elements. SQL statements are
somewhat like simple English sentences. Keywords include SELECT, UPDATE,
WHERE etc. SQL was designed to be entered on a console and results would
display back to a screen. Today, SQL is mostly used by programmers who use
SQL inside their language to build applications that access data in a database. Four
fundamental operations that apply to any database are: * Read the data -- SELECT
* Insert new data -- INSERT * Update existing data -- UPDATE * Remove data --
DELETE
The SQL Command General form:
**
SELECT column-names
FROM table-name
WHERE condition
ORDER BY sort-order
**
Example:
SELECT FirstName,
LastName, City, Country
FROM Customer
WHERE City = 'Dhaka'
ORDER BY FirstName
6 | P a g e

*The SQL INSERT general form
INSERT table-name (columnames)
VALUES (column-values)
 Example
INSERT CUSTOMER (Name,
ContactName, Country,City)
VALUES ('Randevo expo', 'Lina Sam',
'Bangladesh', Dhaka ')
* The SQL UPDATE general form:
UPDATE table-name
SET column-name = columnvalue
WHERE condition
 Example
UPDATE Item
SET Quantity = 3
WHERE Id = 30
* The SQL DELETE general form:
DELETE table-name
WHERE condition
Example
DELETE ORDER
WHERE ID = 38
7 | P a g e

*SQL SELECT STATEMENT:
The SELECT statement retrieves data from a database.The data is returned in a
table-like structure called a result-set. SELECT is the most frequently used action
on a database.
*The SQL general SELECT syntax is:
SELECT column-names
FROM table-name
*Example:
SELECT *
FROM table-name
SQL SELECT Examples:
Problem: List all customers
SELECT * FROM Customer
Results: 91 records
ID F. NAME L.NAME CITY COUNTRY PHONE
1 Shamima Iqbal Alabama USA 01829
2 Tanzila Khan London London 72081
3 Tasnim Zaman Dhaka Bangladesh 24647
4 Mahin Chowdhury Cumilla Bangladesh 83208
5 Arfana Alam California USA 37290
6 Anik Hasan Dhaka Bangladesh 44759
7 Rafsan Ahmed Rajshahi Bangladesh 08795
8 | P a g e

Problem: List the first name, last name, and city of all customers
SELECT FirstName, LastName, City
FROM Customer
F. NAME L.NAME CITY
Shamima Iqbal Alabama
Tanzila Khan London
Tasnim Zaman Dhaka
Mahin Chowdhury Cumilla
Arfana Alam California
Anik Hasan Dhaka
Rafsan Ahmed Rajshahi
Where statement:
* To limit the number of rows use the WHERE clause.
* The WHERE clause filters for rows that meet certain criteria.
* WHERE is followed by a condition that returns either true or false.
* WHERE is used with SELECT, UPDATE, and DELETE
The SQL WHERE syntax:
*A WHERE clause with a SELECT statement:
SELECT column-names
FROM table-name
WHERE condition
9 | P a g e

 A WHERE clause with an UPDATE statement
UPDATE table-name
SET column-name = value
WHERE condition
 A WHERE clause with a DELETE statement:
DELETE table-name
WHERE condition
SQL WHERE Clause Examples
Problem: List the customers in Sweden
SELECT Id, FirstName, LastName,
City, Country, Phone
FROM Customer
WHERE Country = 'USA'
ID F. NAME L.NAME CITY COUNTRY PHONE
1 Shamima Iqbal Alabama USA 01829
5 Arfana Alam California USA 37290
Problem: Update the city to Sydney for customer
10 | P a g e

UPDATE Customer
SET City = 'Alabama'
WHERE Name = 'Shamima'
Problem: Delete all products with unit price higher than $100
DELETE FROM Product
WHERE Unit Price > 100
Note: This deletion may be prevented by referential intergrity
One of the better approach may be to discontinue the product, that is, set is
discontinued to true.
SQL insert into statement:
*The INSERT INTO statement is used to add new data to a database.
* The INSERT INTO statement adds a new record to a table.
* INSERT INTO can contain values for some or all of its columns.
* INSERT INTO can be combined with a SELECT to insert records.
The SQL INSERT INTO syntax
The general syntax is:
INSERT INTO table-name
(column-names)
VALUES (values)
SQL INSERT INTO Examples
11 | P a g e

Problem: Add a record for a new customer
*INSERT INTO Customer (FirstName, LastName, City, Country)
VALUES ('Shamima', 'Iqbal’, 'Alabama', 'USA')
Problem: Add a new customer named Anita Coats to the database
INSERT INTO Customer (FirstName, LastName)
VALUES ('Tanzila', 'Khan')
*The SQL INSERT combined with a SELECT
INSERT INTO table-name (column-names)
SELECT column-names
FROM table-name
WHERE condition
SQL update statement:
*The UPDATE statement updates data values in a database.
*UPDATE can update one or more records in a table.
*Use the WHERE clause to UPDATE only specific records.
The SQL UPDATE syntax
The general syntax is given below:
12 | P a g e