Lesson 13

In this module, you learned the basic Transact-SQL syntax required to create, modify, and delete a SQL Server 2022 database. Having completed this module, you should be able to:

1. List the tools used to create a database
2. Identify the permissions required to create, modify, or delete databases
3. Describe elements of a basic database
4. Issue Transact-SQL statements to create, modify, or delete databases

Glossary Terms: This module introduced you to the following terms:

1. database: In SQL Server 2022, a database is a structured set of data organized for efficient storage, retrieval, and management. It serves as a container for various database objects like tables, views, stored procedures, and indexes, providing a logical separation for different applications or data sets.
2. DBO: In SQL Server 2022, DBO stands for Database Owner, a special user account that has all permissions within a specific database. The DBO can perform any operation within the database, including creating, altering, and deleting objects, as well as managing users and permissions.
3. hierarchy: In SQL Server 2022, a hierarchy refers to a structured relationship between different levels of data, often represented in a parent-child format. This hierarchical structure allows for organizing and querying data in a tree-like manner, which is particularly useful for representing things like organizational charts, product categories, or geographical locations.
4. SA: In SQL Server 2022, SA stands for System Administrator, a special login that has the highest level of privileges on the entire SQL Server instance. The SA can perform any operation on the server, including managing logins, databases, linked servers, and server configuration settings.
5. transaction log:In SQL Server 2022, the transaction log is a critical component that records all changes made to the database, ensuring data integrity and recoverability. It acts as a write-ahead log, meaning changes are written to the log first before being applied to the data files, allowing for rollback or recovery in case of failures.

In the next module, we will investigate column attributes for a SQL Server table.

Here are the Transact-SQL statements to create your database and tables within SQL Server 2022.

-- Create the database
CREATE DATABASE RelationalDBDesign;

-- Use the database
USE RelationalDBDesign;

-- Create the database-analysis table
CREATE TABLE database-analysis (
    analysis_id INT PRIMARY KEY,
    -- Add other columns relevant to database analysis
);

-- Create the database-design table
CREATE TABLE database-design (
    design_id INT PRIMARY KEY,
    -- Add other columns relevant to database design
);

-- Create the extended-features table
CREATE TABLE extended-features (
    feature_id INT PRIMARY KEY,
    -- Add other columns relevant to extended features
);

-- Create the oracle-sql table
CREATE TABLE oracle-sql (
    sql_id INT PRIMARY KEY,
    -- Add other columns relevant to Oracle SQL
);

Explanation

• CREATE DATABASE RelationalDBDesign;: This statement creates a new database named RelationalDBDesign.
• USE RelationalDBDesign;: This switches the active database context to the newly created RelationalDBDesign database.
• CREATE TABLE...: These statements create the four tables you specified: database-analysis, database-design, extended-features, and oracle-sql.
  • Each table has an INT primary key column (e.g., analysis_id, design_id, etc.) for unique identification of rows.
  • You'll need to replace the comments (-- Add other columns...) with the specific columns and data types that are relevant to the information you want to store in each table.

Important Notes

• Choose descriptive column names that clearly indicate the data they will hold.
• Select appropriate data types for each column (e.g., VARCHAR, INT, DATE, DECIMAL, etc.).
• Consider adding constraints (e.g., NOT NULL, UNIQUE, FOREIGN KEY) to enforce data integrity.
• If you have existing data, you can use the INSERT INTO statement to populate the tables after creating them.

Example with Columns: Here's an example with a few columns added to each table to give you a better idea:

-- Create the database-analysis table
CREATE TABLE database-analysis (
    analysis_id INT PRIMARY KEY,
    analysis_name VARCHAR(255),
    analysis_date DATE,
    analysis_notes TEXT
);

-- Create the database-design table
CREATE TABLE database-design (
    design_id INT PRIMARY KEY,
    design_name VARCHAR(255),
    number_of_tables INT,
    design_schema VARCHAR(MAX)
);

-- Create the extended-features table
CREATE TABLE extended-features (
    feature_id INT PRIMARY KEY,
    feature_name VARCHAR(255),
    feature_description TEXT,
    is_implemented BIT
);

-- Create the oracle-sql table
CREATE TABLE oracle-sql (
    sql_id INT PRIMARY KEY,
    sql_statement VARCHAR(MAX),
    sql_purpose VARCHAR(255),
    execution_time DECIMAL(10, 2)
);

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
SQL Server Databases - Quiz