Scribd
Upload
28 views3 pages

SQL Basics: Databases and Queries

This lesson plan introduces SQL and databases, aiming to equip students with the ability to create databases, tables, insert data, and retrieve data using SELECT queries. Key concepts include the definition of SQL and databases, along with practical tasks such as creating a 'Library' database and a 'Books' table. The lesson also covers SQL code examples for creating databases and tables, inserting data, and retrieving information from tables.

Uploaded by

preethiprakash2602
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
28 views3 pages

SQL Basics: Databases and Queries

This lesson plan introduces SQL and databases, aiming to equip students with the ability to create databases, tables, insert data, and retrieve data using SELECT queries. Key concepts include the definition of SQL and databases, along with practical tasks such as creating a 'Library' database and a 'Books' table. The lesson also covers SQL code examples for creating databases and tables, inserting data, and retrieving information from tables.

Uploaded by

preethiprakash2602
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Lesson Plan: Introduction to SQL and Databases

Lesson Objectives:

By the end of this lesson, students will be able to:


[Link] what SQL and databases are.
[Link] a new database.
[Link] tables within a database.
[Link] data into tables.
5. Retrieve data using SELECT queries.

Step 1: Introduction to SQL and Databases

What is SQL?

SQL (Structured Query Language) is a language used to interact with relational databases. It
helps in:
🔹 Storing, retrieving, updating, and deleting data.
🔹 Managing database structures (tables, views, indexes).
🔹 Performing transactions securely.

What is a Database?

A database is a collection of structured data organized for easy access, retrieval, and
management. Examples: MySQL, PostgreSQL, SQLite, SQL Server.

Step 2: Creating a Database

A database needs to be created before storing any data.

SQL Code to Create a Database

Example code:

CREATE DATABASE College;

Explanation:

 CREATE DATABASE is used to create a new database.

 College is the name of the database.

Task 1
🔹 Create a database named Library for managing books, authors, and members.

Step 3: Creating a Table

After creating a database, we need to define tables to store data.

Using the College Database

USE College;

SQL Code to Create a Table:

Example code :

CREATE TABLE Students ( StudentID INT PRIMARY KEY,

Name VARCHAR(100),Age INT,

MajorVARCHAR(50) );

Explanation:

 StudentID is an integer and serves as the primary key (unique identifier).

 Name is a string of up to 100 characters.

 Age stores the student's age.

 Major stores the field of study.

Task 2

🔹 Create a table named Books with columns: BookID, Title, Author, and PublishedYear.

Step 4: Inserting Data into a Table

Once a table is created, we can insert records.

SQL Code to Insert Data:

Example code:

INSERT INTO Students (StudentID, Name, Age, Major)

VALUES (1, 'Alice Johnson', 20, 'Computer Science');

Explanation:
 INSERT INTO Students specifies the table name.

 Column names (StudentID, Name, Age, Major) define where values will be inserted.

 VALUES (...) provides actual data for the columns.

Task 3

🔹 Insert 3 records into the Books table.

Step 5: Retrieving Data Using SELECT Queries

The SELECT statement retrieves data from tables.

SQL Code to Fetch All Students:

Example code:

SELECT * FROM Students;

Explanation:

 (*) means all columns.

 FROM Students specifies the table to retrieve data from.

SQL Code to Fetch Specific Columns:

Example code:

SELECT Name, Major FROM Students;

Explanation:

 Retrieves only Name and Major columns.

Task 4

🔹 Write a query to fetch all book titles and authors from the Books table.

Common questions

Powered by AI

A SELECT statement that specifies particular columns retrieves only those columns from the table, excluding others. For example, 'SELECT Name, Major FROM Students;' will return just the 'Name' and 'Major' columns from the 'Students' table, ignoring all other data fields. This allows for more focused and efficient data retrieval .

SQL is used for storing, retrieving, updating, and deleting data within databases. In addition, it helps manage database structures such as tables, views, and indexes, and perform transactions securely .

'CREATE DATABASE' is used to create a new database, initializing a new environment to store data, as seen in 'CREATE DATABASE College;'. The 'USE' command, on the other hand, sets the context for the session to a specific database, as seen in 'USE College;', indicating that subsequent SQL commands will apply to the 'College' database. While the former initializes, the latter specifies the working environment .

Inserting data into a SQL table involves specifying the table name and the columns into which data will be inserted, followed by the corresponding values. For instance, the code 'INSERT INTO Students (StudentID, Name, Age, Major) VALUES (1, 'Alice Johnson', 20, 'Computer Science');' adds a new record to the 'Students' table. This step is crucial for populating tables with data which can then be queried and analyzed .

A primary key in a SQL table serves as a unique identifier for each record in that table. For example, in the 'Students' table, 'StudentID' is designated as the primary key, ensuring that each student record is uniquely distinguishable by their ID .

SQL manages database transactions by using commands that ensure operations are executed in a secure, consistent, and recoverable manner. This includes the use of BEGIN TRANSACTION, COMMIT, and ROLLBACK commands to process transactions securely, ensuring that all parts of a transaction are completed successfully before changes are committed to the database, or completely undone if any part fails .

To create a table named 'Books' with the specified columns, you would use the SQL code: CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(255), Author VARCHAR(255), PublishedYear INT);. This code defines a table where 'BookID' is a unique identifier for each book, and 'Title', 'Author', 'PublishedYear' are fields for storing the book's title, author, and year of publication, respectively .

To retrieve all book titles and authors from a 'Books' table, you would execute 'SELECT Title, Author FROM Books;'. Specifying columns is important because it reduces the amount of data fetched and thus enhances query performance by retrieving only the necessary fields .

The 'CREATE DATABASE' SQL statement is used to initialize a new database. When 'CREATE DATABASE Library' is executed, it creates a database named 'Library' which can then be used to store structured collections of data such as information on books, authors, and members .

Structured data allows for efficient storage, retrieval, and management within databases, which is essential for performance and data integrity. Examples of databases that manage structured data include MySQL, PostgreSQL, SQLite, and SQL Server. These systems leverage relational models to organize data in tables, enabling advanced queries, indexing, and transaction processing .

You might also like