Sql Creating Databases And Tables Complete Guide

1. How do I create a new database in SQL?

Answer: To create a new database in SQL, you use the CREATE DATABASE command. For instance, if you want to create a database named MyLibrary, you would execute:

CREATE DATABASE MyLibrary;

After creation, you might need to switch to the newly created database using the USE command in some SQL environments:

USE MyLibrary;

Note: In MySQL, you use DATABASE whereas in other SQL environments like SQL Server, SQLite or PostgreSQL, the command is exactly as above.

2. What is the difference between a PRIMARY KEY and a FOREIGN KEY in SQL?

Answer: A PRIMARY KEY uniquely identifies each record in a table, ensuring that no two records have the same key value and that every row has a unique identifier.

A FOREIGN KEY on the other hand, refers to the PRIMARY KEY of another table, facilitating the establishment of a link between the two tables. This is critical for maintaining referential integrity across multiple related tables.

Example:

• In Authors table, AuthorID could be the PRIMARY KEY.
• In Books table, AuthorID could be the FOREIGN KEY.

3. How do I create a table with a PRIMARY KEY and a FOREIGN KEY?

Answer: When creating a table with these keys, first define your PRIMARY KEY in the table that will be referenced. Then, define your FOREIGN KEY in the related table pointing to that PRIMARY KEY.

Here's an example where Authors and Books tables are linked:

CREATE TABLE Authors (
    AuthorID INT NOT NULL,
    AuthorName VARCHAR(255) NOT NULL,
    PRIMARY KEY (AuthorID)
);

CREATE TABLE Books (
    BookID INT NOT NULL,
    Title VARCHAR(255) NOT NULL,
    AuthorID INT NOT NULL,
    PRIMARY KEY (BookID),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

4. Can I specify a column as NOT NULL when creating a table?

Answer: Yes, definitely! The NOT NULL constraint mandates that a column cannot have a NULL value.

Example:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    BirthDate DATE
);

In this table, both EmployeeID and LastName columns are required fields.

5. How do I define a column to automatically increment its value upon insertion of a new record?

Answer: Many SQL systems offer an AUTO_INCREMENT, SERIAL, or IDENTITY keyword to automatically increment values in a column whenever a new row is inserted.

MySQL Example:

CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT,
    OrderNumber INT NOT NULL,
    CustomerID INT NOT NULL,
    Primary KEY (OrderID)
);

PostgreSQL Example:

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    OrderNumber INT NOT NULL,
    CustomerID INT NOT NULL
);

6. Can I add more than one column with constraints when creating a table?

Answer: Absolutely, you can define multiple columns with various constraints during the creation of a table.

Example:

CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT NOT NULL,
    FullName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15),
    BirthDate DATE CHECK (BirthDate <= CURDATE()),
    GPA NUMERIC(3, 2) CHECK (GPA >= 0 AND GPA <= 4),
    PRIMARY KEY (StudentID)
);

In this schema, FullName and Email must be provided; Email must be unique; BirthDate should not be in the future, and GPA should be a valid grade between 0 and 4.

7. How do I create a table with a composite primary key made from two columns?

Answer: Composite primary keys consist of two or more columns that together uniquely identify a row in a table. Here is how you'd set one up:

Example:

CREATE TABLE CourseRegistrations (
    CourseID INT NOT NULL,
    StudentID INT NOT NULL,
    RegistrationDate DATE,
    PRIMARY KEY (CourseID, StudentID)
);

In this case, no two rows can have the same combination of CourseID and StudentID.

8. How do I create an index in SQL for faster searching?

Answer: Indexes speed up the process of data retrieval but may slow down data modification operations. You can create them using the CREATE INDEX command.

Example:

To create an index on the LastName column in the Employees table:

CREATE INDEX idx_lastname
ON Employees (LastName);

For a table with a composite index:

CREATE INDEX idx_student_course
ON CourseRegistrations (StudentID, CourseID);

9. Can I specify a DEFAULT value for a column when inserting records into a table?

Answer: Yes, you can specify a default value for a column by using the DEFAULT keyword. This value gets used automatically if no specific value is provided for the column during an insert operation.

Example:

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT NOT NULL,
    CustomerName VARCHAR(255) NOT NULL,
    Country VARCHAR(255) DEFAULT 'Unknown',
    PRIMARY KEY (CustomerID)
);

In this instance, if Country is not specified while inserting a record, it defaults to 'Unknown'.

10. How do I ensure that the data entered into a table column conforms to certain conditions?

Answer: SQL provides several ways to enforce rules when inserting or updating data: CHECK constraints, UNIQUE constraints, and FOREIGN KEY constraints.

• CHECK Constraint: Used to limit the values allowed for a given column.

CREATE TABLE Suppliers (
    SupplierID INT NOT NULL,
    SupplierName VARCHAR(255) NOT NULL,
    CreditLimit NUMERIC(10,2) CHECK (CreditLimit > 0),
    PRIMARY KEY (SupplierID)
);

• UNIQUE Constraint: Ensures that all values in a column are distinct from one another.

CREATE TABLE Employees (
    EmployeeID INT NOT NULL AUTO_INCREMENT,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Email VARCHAR(255) UNIQUE NOT NULL,
    PRIMARY KEY (EmployeeID)
);

• FOREIGN KEY Constraint: Ensures that foreign key values match primary keys in another table. We discussed this earlier in FAQ #2 and #3.

Applying these kinds of constraints helps maintain the integrity and consistency of the database.