DATABASE SYSTEMS II
Chapter 3
SQL Fundamentals
Amoud University
Faculty of Computing and ICT
Senior BBIT
Lecturer : Samakaab Basha
Create First Database
Creating a database inside of SQL Enterprise has its
advantages. After launching Microsoft's SQL Server
Management Studio Enterprise application, simply
right-clicking on the Databases folder of the Object
Explorer gives you the option to create a New
Database. After selecting the New Database... option,
name your database "MyDatabase" and press "OK
WHAT IS SQL?
SQL (Structured Query Language) is a
programming language used to communicate
with data stored in a relational database
management system. SQL syntax is similar to the
English language, which makes it relatively easy
to write, read, and interpret.
Create Database Query:
Create Database Mydatabase;
Create Database Test;
Create Database AmoudICT;
To this query you should
Press Execute button or striking the (F5) key
Cont..
After executing this query, SQL will
notify you that your query has run
successfully and that the database was
created successfully. If you receive an
error message instead, Google the error
message for troubleshooting advice
TABLES
Data is stored inside SQL tables which are contained
within SQL databases. A single database can house
hundreds of tables, each playing its own unique role in
the database schema.
SQL tables are Consist of table rows and columns.
Table columns is Vertical and responsible for storing
many different types of data, like numbers, texts, dates,
and even files. There are many different types of table
columns and these data types vary, depending on how the
SQL table has been created by the SQL developer.
A table row is a horizontal record of values that fit into
each different table column.
Create a table Using SSMS
Create this table
Table Name: Employee
Employe Empnam Gende Department Salar
eID e r y
2290 Abdirahm male accounting 1000
an
4015 Abdikaf male sales 900
1726 Amina female Human 850
Resource
8153 Mohamed male Finance 1200
5142 Osman male marketing 900
9017 Fatima female Sales 550
4111 Sulekha female marketing 700
9
Create Table Query:
CREATE TABLE TestTable
( ID Nvarchar (50) ,
Name Nvarchar (50),
Age Nvarchar (50),
Class Nvarchar (50),
Phone Nvarchar (50) );
DML
DML is abbreviation of Data Manipulation
Language.
This is used to manipulate data stored in the database.
DML work on the data stored not on the structure of
database object.
Types of DML
SELECT: Retrieves data from a table
UPDATE: Updates existing data into a table
INSERT: Inserts data into a table
DELETE: Deletes all records from a table
SELECT
The SELECT statement is used to query the database and retrieve
selected data that match the criteria that you specify.
SELECT (*) is a shortcut that can be used to select all table
columns rather than listing each of them by name.
Examples
SELECT *
SELECT *
FROM Table1;
FROM TestTable; Where ID = ‘1103’
SELECT Age, Phone
FROM Table1;
UPDATE
The UPDATE statement is used to update existing records in a
table, An SQL UPDATE statement changes the data of one or more
records in a table. Either all the rows can be updated, or a subset
may be chosen using a condition,
Examples
UPDATE TestTable UPDATE TestTable
SET Age= ‘15’ SET City= ‘Borama’
WHERE Name = ‘Ali';
INSERT
The SQL INSERT INTO Statement. The INSERT INTO statement
is used to insert new records in a table.
The SQL INSERT statement allows you to insert a single record or
multiple records into a table.
Examples
INSERT INTO TestTable
(ID, Name, Age)
VALUES( ‘11’, ‘Muuse’,’35’);
DELETE
The DELETE Statement removes selected rows from a
table.
Examples
DELETE
from TestTable
WHERE ID = ‘10’
DDL
It is abbreviation of Data Definition Language
Are used to build and modify the structure of your
tables and other objects in the database. When you
execute a DDL statement, it takes effect immediately.
Some of the DDL commands are CREATE, ALTER,
DROP, RENAME.
Types of DDL
CREATE: Creates Objects in the Database.
Alter: Alters Objects of the Database.
Rename: Change the Name of a Table to a Different Name.
Drop: Deletes Objects of the Database.
Create
A CREATE statement is used to create database and database
objects like tables and Queries.
Examples
CREATE TABLE Test
( ID Nvarchar (50) ,
Name Nvarchar (50),
Age Nvarchar (50),
Class Nvarchar (50),
Phone Nvarchar (50) );
ALTER
The ALTER statement helps to modify an existing DB
object.
Examples
ALTER TABLE employee ALTER TABLE employee
DROP COLUMN Age;
ADD City nvarchar;
ALTER TABLE employee
ALTER COLUMN EmployeeID nvarchar;
RENAME
This statement lets you change the name of a table to a
different name.
Examples
ALTER TABLE employee RENAME TO workers
DROP
The DROP command of the Data Definition Language,
allows us to remove entire database, table, view or an
index objects from our DBMS.
Examples
DROP TABLE Employee
END
