101.school
CoursesAbout
Search...⌘K
Generate a course with AI...

    How Databases work

    Receive aemail containing the next unit.
    • Introduction to Databases
      • 1.1What is a Database?
      • 1.2Importance of Databases
      • 1.3Types of Databases
    • Database Models
      • 2.1Hierarchical Model
      • 2.2Network Model
      • 2.3Relational Model
      • 2.4Object-oriented Model
    • Relational Databases
      • 3.1Introduction to Relational Databases
      • 3.2Tables, Records, and Fields
      • 3.3Keys and Indexes
    • SQL Basics
      • 4.1Introduction to SQL
      • 4.2Basic SQL Commands
      • 4.3Creating and Modifying Tables
    • Advanced SQL
      • 5.1Joins
      • 5.2Subqueries
      • 5.3Stored Procedures
    • Database Design
      • 6.1Normalization
      • 6.2Entity-Relationship Diagrams
      • 6.3Data Integrity
    • Transaction Management
      • 7.1ACID Properties
      • 7.2Concurrency Control
      • 7.3Recovery Techniques
    • Database Security
      • 8.1Security Threats
      • 8.2Access Control
      • 8.3Encryption and Authentication
    • NoSQL Databases
      • 9.1Introduction to NoSQL
      • 9.2Types of NoSQL Databases
      • 9.3Use Cases for NoSQL
    • Big Data and Databases
      • 10.1Introduction to Big Data
      • 10.2Big Data Technologies
      • 10.3Big Data and Databases
    • Cloud Databases
      • 11.1Introduction to Cloud Databases
      • 11.2Benefits and Challenges
      • 11.3Popular Cloud Database Providers
    • Database Administration
      • 12.1Roles and Responsibilities of a Database Administrator
      • 12.2Database Maintenance
      • 12.3Performance Tuning
    • Future Trends in Databases
      • 13.1In-memory Databases
      • 13.2Autonomous Databases
      • 13.3Blockchain and Databases

    SQL Basics

    Creating and Modifying Tables in SQL

    In this unit, we will delve into the specifics of creating and modifying tables in SQL. Tables are the primary structures where data is stored in relational databases. Understanding how to create, modify, and delete tables is crucial for anyone working with databases.

    Creating Tables

    The CREATE TABLE statement is used to create a new table in a database. The syntax for creating a table is as follows:

    CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );

    Each column in the table is specified with a name (e.g., column1) and a datatype (e.g., INT, DATE, VARCHAR), which defines the type of data that column can hold.

    Modifying Tables

    Once a table is created, you may need to modify it. The ALTER TABLE statement is used to add, delete/drop or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

    To add a column:

    ALTER TABLE table_name ADD column_name datatype;

    To delete a column:

    ALTER TABLE table_name DROP COLUMN column_name;

    To modify the data type of a column:

    ALTER TABLE table_name MODIFY column_name column_type;

    Deleting Tables

    The DROP TABLE statement is used to drop an existing table in a database. Be careful with this statement! Once a table is deleted, all the information available in the table is lost.

    DROP TABLE table_name;

    Constraints in SQL

    Constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

    Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

    The following constraints are commonly used in SQL:

    • PRIMARY KEY: Uniquely identifies each record in a table.
    • FOREIGN KEY: Uniquely identifies a record in another table.
    • NOT NULL: Ensures that a column cannot have a NULL value.
    • UNIQUE: Ensures that all values in a column are different.
    • CHECK: Ensures that all values in a column satisfy certain conditions.

    By understanding these commands and constraints, you can effectively manage your tables in SQL, ensuring that your data is organized, consistent, and reliable.

    Test me
    Practical exercise
    Further reading

    Buenos dias, any questions for me?

    Sign in to chat
    Next up: Joins