Section outline
-
-
Students mustView
-
-
Message......
Welcome students to our online learning center. Here you will be provided with the course contents for your best studies. We look forward to your best of efforts and a good association of studies with you.
Course Description:
This lab is focused on the various contents of Database SQL including database and table create, data definition language, data modification language, different SQL query, join, sub query and aggregate function. This course introduces a methodical approach to program development, starting from problem formulation and requirement specification, through design of the logical solution, implementation error finding and evaluation of the solution with documentation.
Course Methodology:
- Class size of 35-40 Students
- Classroom with a projector, a computer and a white marker board
- Lab with a personal computer for each student and internet connection
- Students will go through sufficient number of assignments and lab works
- Students may need to attend seminar and workshop during the turner of the course
Course Objectives (CO’s):
- create database, table and insert data and basic modification
- solve problems using conditional statement
- solve problems using Join, Sub-query, Aggregate function and View
- learn the techniques for presenting the pre-defined task.
Course Content:
Lesson 01: DDL-Design database and table with proper data set [Session 1 & 2]
Lesson02: DML-Problem solving basic condition [Session 3 & 4]
Lesson03: Problem solving using Aggregate Function [Session 5 & 6]
Lesson04: Problem solving using Sub Query [Session 7]
Lesson05: Problem solving using Join Operation [Session 8]
Lesson06: Problem solving using different combination of query [Session 9]
Lesson07: Storing SQL query in a DB [Session 10]
Assessment Plan:
SL No.
Assessment Method
Marks Distribution
Remarks
01
Continuous Assessment
Attendance
10%
To measure how well students have learned throughout the semester.
Assignment
25%
Lab Performance
25%
02
Examination
Lab Mid Term
20%
To measure how far students have achieved the learning outcomes.
Lab Final Term
20%
-
Students mustView
This tool helps those who wants to use online tool for practicing
-
Topics Objective:
- Understanding the basic concepts of Database.
- How to manage database system
Topics Outcomes:
- Able to handle database through software.
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulate databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
Some of The Most Important SQL Commands
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index
-
-
Students mustViewMake a submission
1. Install sql server or mysql in your Pc
2. Take screenshot
3. Upload it google drive
4. submit link here
https://www.youtube.com/watch?v=WuBcTJnIuzo
-
Students mustView
-
-
Students mustView
-
Topics Objective:
Understand How to create DB, table and insert data.
Topics Outcomes:
Able to create database.
Able to create table with proper primary and foreign key.
Able to insert data.
The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
Syntax
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
Look at the following two tables:
"Persons" table:
Syntax
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);-
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
This Alter belongs to followings:
- ADD Column
- DROP COLUMN
- ALTER/MODIFY COLUMN
- Change Data Type
- ADD Column
-
-
Students mustView
-
Topics Objective
- Explain how to solve problem using Basic Where, AND, OR, NOT, Order By
Topics Outcomes
- Able to implement condition while solving problem using Basic Where, AND, OR, NOT, Order By
The SQL WHERE Clause
The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, ... FROM table_name WHERE condition;
Example
SELECT * FROM Customers WHERE Country='Mexico';
SELECT * FROM Customers WHERE CustomerID=1;
The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
- The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
-
Students mustViewMake a submissionAfter completing all tasks upload those tasks in drive and upload only drive link here
-
Topics Objective
- Explain how to solve problem using Like, IN, Between, Top Operator
Topics Outcomes
- Able to implement condition while solving problem using Like, IN, Between, Top Operator
The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:
- % - The percent sign represents zero, one, or multiple characters
- _ - The underscore represents a single character
LIKE Syntax
SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that start with "a" WHERE CustomerName LIKE '%a' Finds any values that end with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o" The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
IN Syntax
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
The SQL SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
Top Syntax
SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
-
Topics Objective
- Understand to solve problem using different aggregate functions like as Minimum, Maximum, Average, Sum, Count
- Explain the process that help to implement Group By, Having function
Topics Outcomes
- Able to solve problem using different aggregate functions
- Able to implement Group By, Having function
The SQL MIN(), MAX(), COUNT(), AVG() and SUM() Functions
- The MIN() function returns the smallest value of the selected column.
- The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
MAX() Syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
COUNT() Syntax
SELECT COUNT(column_name) FROM table_name WHERE condition;
AVG() Syntax
SELECT AVG(column_name) FROM table_name WHERE condition;
SUM() Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s);
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
HAVING Syntax
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition;
-
Topics Objective
- Understand how to join multiple tables using different types of join.
Topics Outcomes
- Able to solve problem using different types of join.
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
-
The INNER JOIN keyword selects records that have matching values in both tables.
-
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
-
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
-
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Note:
- FULL OUTER JOIN can potentially return very large result-sets!
- FULL OUTER JOIN and FULL JOIN are the same.
-
-
-
Students mustViewMake a submission
1. After answering each question, take picture
2. Upload all the pictures in google doc numbers wise
3. share google form link in my mail address(momo.nusrat@gmail.com)
4. You can choose foreign key in invoice table
-
-
-
Students mustMark as done
-
-
-
Students mustMark as done
1. After answering each question, take picture
2. Upload all the pictures in google doc numbers wise
3. share google form link in my mail address(momo.nusrat@gmail.com)
-
-
-
Students mustMark as done
-
