Step-by-Step Guide to Creating and Querying a SQL Server Database - Day 1

How to Create and Manage a SQL Server Database: A Step-by-Step Guide

Managing a database in SQL Server is a fundamental skill for database administrators and developers. This guide walks you through creating a database, connecting to it, creating tables, inserting data, and executing various queries to retrieve meaningful information.

TASK 1: CREATE A DATABASE
To create a new database in SQL Server, use the CREATE DATABASE statement. This statement initializes a new database that can store tables, indexes, and other database objects.

CREATE DATABASE University_Database;

Explanation:

• The CREATE DATABASE statement initializes a new database named University_Database.
• This command allocates necessary disk space for the database and prepares it for use.

TASK 2: CONNECT TO THE DATABASE
After creating the database, you need to connect to it before executing any further SQL statements. Use the USE statement to switch to the newly created database.

USE University_Database;

Explanation:

• The USE statement tells SQL Server that all subsequent operations should be performed on University_Database.

TASK 3: CREATE A TABLE AND INSERT DATA
Now, let's create a table called Courses and insert sample data into it.

Create Table

CREATE TABLE Courses (
    Course_ID INT,
    CourseName CHAR(30),
    CourseDuration INT
);

Explanation:

• CREATE TABLE defines a new table named Courses.
• Course_ID INT specifies a column to store unique course IDs.
• CourseName CHAR(30) defines a column to store course names.
• CourseDuration INT represents the course duration in days.

Insert Data into the Table

INSERT INTO Courses VALUES (101, 'COMPUTERS', 180);
INSERT INTO Courses VALUES (102, 'ROBOTICS', 180);
INSERT INTO Courses VALUES (103, 'CIVIL123$', 180);
INSERT INTO Courses VALUES (104, 'MECHATRONICS', 120);

Explanation:

• The INSERT INTO statement is used to add new rows to the Courses table.
• Each row contains a unique Course_ID, a CourseName, and a CourseDuration.

Verify Data Insertion

SELECT * FROM Courses;

This command retrieves all rows from the Courses table, allowing you to confirm successful data insertion.

---

TASK 4: Retrieve Courses Starting with 'C'

SELECT * FROM Courses WHERE CourseName LIKE 'C%';

Explanation:

• The LIKE 'C%' filter selects all courses where the CourseName starts with the letter 'C'.

TASK 5: Retrieve Courses Ending with 'S'

SELECT * FROM Courses WHERE CourseName LIKE '%S';

Explanation:

• The LIKE '%S' filter selects all courses where the CourseName ends with the letter 'S'.

TASK 6: Retrieve Courses with Duration Above 150 Days

SELECT * FROM Courses WHERE CourseDuration > 150;

Explanation:

• The > operator filters courses with durations greater than 150 days.

TASK 7: Retrieve Courses with Duration Above 150 OR Below 130

SELECT * FROM Courses WHERE CourseDuration > 150 OR CourseDuration < 130;

Explanation:

• The OR operator includes courses meeting either of the conditions.

TASK 8: Retrieve Courses with Duration Above 150 OR Below 130 AND Name Starts with 'C'

SELECT * FROM Courses WHERE (CourseDuration > 150 OR CourseDuration < 130) AND CourseName LIKE 'C%';

Explanation:

• The parentheses ensure that the AND condition applies correctly to the OR conditions.

TASK 9: Retrieve Courses Where Name Does Not Start with 'C'

SELECT * FROM Courses WHERE CourseName NOT LIKE 'C%';

Explanation:

• The NOT LIKE condition filters out course names starting with 'C'.

TASK 10: Retrieve Courses Starting with 'C' and Duration Above 150

SELECT * FROM Courses WHERE CourseName LIKE 'C%' AND CourseDuration > 150;

Explanation:

• The AND condition ensures both criteria are met.

---

Conclusion
This guide demonstrated how to:

• Create and connect to a SQL Server database.
• Define and populate a table.
• Run various SQL queries to filter and retrieve data efficiently. By mastering these operations, you can manage databases and extract meaningful insights from data effectively.