SQL Database Operations: Create, Query, Modify Data

SQL Database and Table Creation

These commands demonstrate how to create a new database and define a table structure within it.

Creating the BDHotel Database

USE master;
GO
CREATE DATABASE BDHotel
ON
(
    NAME = 'BDHotel',
    FILENAME = 'c:\MSSQL7\Data\BDHotel.mdf',
    SIZE = 4MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
);

Creating the ROOM Table

USE BDHotel;
GO
CREATE TABLE ROOM
(
    NHabit INT NOT NULL,
    Building CHAR(1) NOT NULL,
    NumCamas INT NOT NULL DEFAULT (2),
    Available CHAR(1) NOT NULL,
    PRIMARY KEY (NHabit),
    CHECK (Building IN ('A', 'B', 'C', 'D', 'E'))
);

SQL Data Query Language (DQL)

The SELECT statement is used to retrieve data from a database. Here are various ways to query data.

Basic Data Retrieval (SELECT)

1. Select All Columns and Rows

   Retrieve all fields (columns) and tuples (rows) from the Student table:

   SELECT * FROM Student;

   Alternatively, specify all column names:

   SELECT IDalumno, surname, name, address, phone, age FROM Student;

2. Select Specific Columns

   Retrieve only some fields (columns) from the Student table:

   SELECT IDalumno, name, surname FROM Student;

3. Select Filtered Rows (WHERE Clause)

   Retrieve all or some fields (columns) and specific tuples (rows) from the Student table based on criteria:

   • Filter by exact match:

     SELECT * FROM Student WHERE address = 'Chitre';

   • Filter by numerical comparison:

     SELECT * FROM Student WHERE age <= 30;

   • Filter by pattern matching (LIKE operator):

     SELECT * FROM Student WHERE name LIKE 'M%';

   • Filter by date comparison (example with a Course table):

     SELECT * FROM Course WHERE fecha_inicio >= '2009-05-01';

     Note: Date format adjusted for common SQL practice. Original was 01/05/2009.

4. Retrieve Unique Values (DISTINCT)

   Perform queries to get fields without displaying duplicate values:

   SELECT DISTINCT address FROM Student;

SQL Data Manipulation (DML) and Structure Modification (DDL)

Beyond basic queries, SQL allows for ordering results, inserting new data, updating existing records, deleting data, and altering table structures.

Ordering Query Results (ORDER BY)

Order the result set by one or more fields (columns) in the table:

SELECT name, surname, age FROM Student ORDER BY age;

When no sort order is specified, ascending (ASC) is assumed. To sort in descending order:

SELECT name, surname, age FROM Student ORDER BY age DESC;

Other examples, sorting by multiple fields or column positions:

SELECT name, surname, age FROM Student ORDER BY age, name;

SELECT name, surname, age FROM Student ORDER BY 3, 2; -- Orders by 3rd column, then 2nd

SELECT * FROM Student ORDER BY name DESC, age ASC;

Data Manipulation Language (DML) Commands

1. Inserting Records (INSERT)

   Add new rows of data into a table:

   INSERT INTO Student (IDalumno, surname, name, address, telephone_number, age)
   VALUES ('A22', 'Meier', 'Christian', 'Lima', '123-4567', 38);

   Note: Corrected 'telephone number' to 'telephone_number' for valid column naming.

2. Updating Data (UPDATE)

   Modify existing data in a table based on specified criteria:

   • Update a specific record:

     UPDATE Student SET address = 'Chitre' WHERE IDalumno = 'A22';

   • Update multiple records based on a condition:

     UPDATE Student SET age = age + 1 WHERE age >= 18;

3. Deleting Data (DELETE)

   Remove rows of data from a table based on specified criteria:

   DELETE FROM Student WHERE IDalumno = 'A22';

Data Definition Language (DDL) - Altering Table Structure

Modify the structure of an existing table using ALTER TABLE commands.

• Modifying Column Data Type

  Change the data type or size of an existing column. For example, changing NumCamas in the ROOM table:

  ALTER TABLE ROOM ALTER COLUMN NumCamas CHAR(20);

  (Original text was "ADD FIELDS: ALTER TABLE ALTER COLUMN BED Type char (20)". Interpreted as modifying an existing column's type.)

• Modifying Column Size

  Adjust the size of a column. For example, changing the size of the Building column in the ROOM table:

  ALTER TABLE ROOM ALTER COLUMN Building CHAR(2);

  (Original text was "Edit: ALTER table ALTER COLUMN BED building char (2)". Interpreted as modifying the Building column.)

• Dropping a Column

  Remove an existing column from a table. For example, removing NumCamas from the ROOM table:

  ALTER TABLE ROOM DROP COLUMN NumCamas;

  (Original text was "DISPOSAL: ALTER TABLE DROP COLUMN NumCamas ROOM". Corrected syntax.)