Practice 02.
SQL Basics
Outline
Creating your first table 1
Naming conventions 1
Understanding schemas 3
Understanding SQL Datatypes 5
Understanding column properties 8
Creating tables 8
Do it yourself with Employee table 10
Altering table 10
Understanding computed columns 11
Adding constraints to a table 12
Create DB diagram 17
Practice with the below databases 18
I. Creating your first table
Naming conventions
The above tables does not have naming convention
===>
The above tables have naming convention.
Understanding schemas
While a database is the primary container of all objects, schemas offer another level of
containment and organization within a database.
Using a schema, a user can group objects of similar scope or ownership together By default, the
database owner (dbo) schema is automatically created within a database. Any object that is
created is added to this schema.
Understanding SQL Datatypes
Numeric
Exact numeric data types
● With fixed precision and scale, there are more data types: decimal and numeric.
decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
p (precision)
The maximum total number of decimal digits that will be stored, both to the left and to
the right of the decimal point. The precision must be a value from 1 through the
maximum precision of 38. The default precision is 18.
s (scale)
The number of decimal digits that will be stored to the right of the decimal point. This
number is subtracted from p to determine the maximum number of digits to the left of
the decimal point. Scale must be a value from 0 through p. Scale can be specified only
if precision is specified. The default scale is 0; therefore, 0 <= s <= p.
Example: decimal(4,2): To store a four-digit number with only two digits to the right of the
decimal place
Approximate numeric data types
● Approximate-number data types for use with floating point numeric data. Floating
point data is approximate; therefore, not all values in the data type range can be
represented exactly. The ISO synonym for real is float(24).
float [ (n) ] Where n is the number of bits that are used to store the mantissa of the float
number in scientific notation and, therefore, dictates the precision and storage size. If n
is specified, it must be a value between 1 and 53. The default value of n is 53.
STRING
Date and time data types
Understanding column properties
● Allow null
● Primary key
● Length
● Unique
● Identity
Creating tables
Do it yourself with Employee table
Employee(EmployeeID, Firstname, MiddleName, LastName)
EmployeeID: int, not null, identity (1,1)
Firstname: varchar, 50, Not null
MiddleName varchar, 50, null
Lastname, varchar, 50, not null
Altering table
❏ Add one more column Gender to Employee table
Understanding computed columns
❏ Add one more column Fullname = FirstName + ‘, ‘ + LastName to Employee table
Adding constraints to a table
Primary key constraints
Default constraints
Unique constraints
Check constraints
❏ Set EmployeeID property as primary key
❏ Set Default value (1) for Active property
❏ Set SocialSecurityNumber property as Unique value
❏ Create check constraint Gender = Female or Male
Foreign key constraints
❏ Creating foreign key constraint between Address table and Employee table, namely,
between EmployeeID on Address table to EmployeeID on Employee table.
Create DB diagram
Practice with the HR database
SQL: https://www.w3resource.com/sql-exercises/sorting-and-filtering-hr/index.php
1. Write a query in SQL to display the full name (first and last name), and salary
for those employees who earn below 6000.
2. Write a query in SQL to display the first and last_name, department number
and salary for those employees who earn more than 8000.
3. Write a query in SQL to display the first and last name, and department
number for all employees whose last name is “McEwen”.
4. Write a query in SQL to display all the information for all employees without
any department number
5. Write a query in SQL to display all the information about the department
Marketing.
Homework
https://en.wikibooks.org/wiki/SQL_Exercises/The_Hospital
https://www.w3resource.com/sql-exercises/hospital-database-exercise/sql-exerci
se-on-hospital-database.php
