SQL Fundamentals and Data Definition Commands
29 Questions
2 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does SQL stand for?

  • Structured Quick Language
  • Simple Query Language
  • Structured Query Language (correct)
  • System Query Language

    • The DROP DATABASE command is used to create a new database.

    False

    What is the purpose of the CREATE TABLE command?

    To create a new table in a database

    The command to remove an existing table is called ______.

    <p>DROP TABLE</p> Signup and view all the answers

    Match the SQL command with its function:

    <p>CREATE DATABASE = Creates a new database DROP DATABASE = Deletes an existing database CREATE TABLE = Creates a new table in a database DROP TABLE = Deletes an existing table in a database</p> Signup and view all the answers

    Which of the following is an example of the CREATE DATABASE command?

    <p>CREATE DATABASE myDB;</p> Signup and view all the answers

    MySQL is a type of Database Management Tool.

    <p>True</p> Signup and view all the answers

    Provide an example of a command to create a table named 'Students'.

    <p>CREATE TABLE Students (StudentID varchar(11), LastName varchar(99), FirstName varchar(99), Section varchar(5));</p> Signup and view all the answers

    What is the purpose of a PRIMARY KEY constraint?

    <p>To uniquely identify a row in a table</p> Signup and view all the answers

    A FOREIGN KEY constraint can be created using the ALTER TABLE command.

    <p>True</p> Signup and view all the answers

    What SQL command is used to add a default value to a column in an existing table?

    <p>ALTER TABLE</p> Signup and view all the answers

    The SQL command to create a FOREIGN KEY constraint in a new table is __________.

    <p>CREATE TABLE</p> Signup and view all the answers

    Match the following SQL constraints with their descriptions:

    <p>PRIMARY KEY = Uniquely identifies a row in a table FOREIGN KEY = Establishes a relationship between tables DEFAULT = Sets a default value for a column NOT NULL = Ensures a column cannot contain NULL values</p> Signup and view all the answers

    Which command is used to remove all records from a table without logging individual row deletions?

    <p>TRUNCATE TABLE</p> Signup and view all the answers

    The ALTER TABLE command can only be used to delete columns from a table.

    <p>False</p> Signup and view all the answers

    What datatype is used for storing date and time values in SQL?

    <p>date, datetime, time</p> Signup and view all the answers

    To add a new column to a table, the syntax is ALTER TABLE table_name ADD ______ datatype;

    <p>column</p> Signup and view all the answers

    Match the following SQL commands with their purposes:

    <p>TRUNCATE TABLE = Remove all records from a table ALTER TABLE = Modify the structure of a table DROP TABLE = Delete a table including its data ADD COLUMN = Introduce a new column to a table</p> Signup and view all the answers

    Which of the following is NOT a common data type in SQL?

    <p>characteristic</p> Signup and view all the answers

    The varchar data type is used for fixed-length character strings.

    <p>False</p> Signup and view all the answers

    What command would you use to change the data type of an existing column in SQL?

    <p>ALTER TABLE table_name ALTER COLUMN column datatype;</p> Signup and view all the answers

    Which SQL operator is used for arithmetic operations?

    <ul> <li></li> </ul> Signup and view all the answers

    The UNIQUE constraint allows duplicate values in a column.

    <p>False</p> Signup and view all the answers

    What is the purpose of the PRIMARY KEY in a CREATE TABLE statement?

    <p>To uniquely identify each row in a table.</p> Signup and view all the answers

    The SQL command to modify an existing table structure is called ________.

    <p>ALTER TABLE</p> Signup and view all the answers

    Match the SQL constraints to their functions:

    <p>CHECK = Ensures values meet a specific condition UNIQUE = Prevents duplicate values in a column DEFAULT = Sets a default value when none is provided PRIMARY KEY = Uniquely identifies each row in a table</p> Signup and view all the answers

    Which constraint can be added to ensure that all values in a column of an existing table are greater than or equal to 15?

    <p>CHECK</p> Signup and view all the answers

    The DEFAULT clause can only be set during the creation of a table.

    <p>False</p> Signup and view all the answers

    Provide an example of how to add a UNIQUE constraint to the StudentID column of the Students table.

    <p>ALTER TABLE Students ADD UNIQUE (StudentID);</p> Signup and view all the answers

    Study Notes

    SQL Fundamentals

    • SQL stands for Structured Query Language, used for managing data in relational database management systems (DBMS).
    • Pronunciation can vary: S-Q-L or "sequel."
    • Key functionalities include creating database structures, manipulating data, and querying databases for information.

    SQL Data Definition Commands

    • CREATE DATABASE

      • Purpose: Creates a new database.
      • Syntax: CREATE DATABASE database_name;
      • Example: CREATE DATABASE myDB;

    • DROP DATABASE

      • Purpose: Deletes an existing database.
      • Syntax: DROP DATABASE database_name;
      • Example: DROP DATABASE myDB;

    • CREATE TABLE

      • Purpose: Creates a new table within a database.
      • Syntax: CREATE TABLE table_name (column1 datatype, …);
      • Example: CREATE TABLE Students (StudentID varchar(11), LastName varchar(99), FirstName varchar(99), Section varchar(5));

    • DROP TABLE

      • Purpose: Deletes an existing table from a database.
      • Syntax: DROP TABLE table_name;
      • Example: DROP TABLE Students;
      • TRUNCATE TABLE: Deletes all data in a table but retains its structure.
        • Syntax: TRUNCATE TABLE table_name;
        • Example: TRUNCATE TABLE Students;

    • ALTER TABLE

      • Purpose: Allows modifications to an existing table's structure.
      • Add column: ALTER TABLE table_name ADD column datatype;
        • Example: ALTER TABLE Students ADD MiddleName varchar(99);
      • Delete column: ALTER TABLE table_name DROP COLUMN column;
        • Example: ALTER TABLE Students DROP COLUMN Section;
      • Modify column: ALTER TABLE table_name ALTER COLUMN column datatype;
        • Example: ALTER TABLE Students ALTER COLUMN MiddleName nvarchar(99);

    SQL Data Types

    • Exact Numeric Types: bigint, bit, decimal, int, money, numeric.
    • Approximate Numeric Types: float, real.
    • Date and Time Types: date, datetime, time.
    • Character String Types: char, text, varchar.
    • Unicode Character String Types: nchar, ntext, nvarchar.
    • Binary String Types: binary, image, varbinary.
    • Other Data Types: cursor, sql_variant, table, uniqueidentifier, xml.

    SQL Operators

    • Arithmetic Operators: +, -, *, /, %.
    • Comparison Operators: =, >, <, >=, <=, <>.

    SQL Constraints

    • CHECK Constraint: Ensures all values in a column meet a specific condition.

      • Syntax: ALTER TABLE table_name ADD CHECK (condition);
      • Example: ALTER TABLE Students ADD CHECK (Age>=15);

    • UNIQUE Constraint: Ensures all values in a column are unique.

      • Syntax: ALTER TABLE table_name ADD UNIQUE (column);
      • Example: ALTER TABLE Students ADD UNIQUE (StudentID);

    • PRIMARY KEY Constraint: Uniquely identifies each row in a table.

      • Example for creation: CREATE TABLE Students (StudentID varchar(11) NOT NULL PRIMARY KEY, ...);
      • Example for alteration: ALTER TABLE Students ADD PRIMARY KEY (StudentID);

    • FOREIGN KEY Constraint: Links a column to a primary key in another table.

      • Example for creation: CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID));
      • Example for alteration:
        • Syntax: ALTER TABLE table1_name ADD FOREIGN KEY (column) REFERENCES other_table (referenced_column);
    • Microsoft SQL Server
    • MySQL
    • Oracle RDBMS
    • Microsoft Access

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    Introduction To SQL (Part 1) PDF

    Description

    This quiz covers the basics of SQL, focusing on data definition commands. You'll explore how to create and drop databases and tables, along with their syntax and examples. Test your knowledge of structured query language and its key functionalities in database management.

    More Like This

    SQL Database Management
    279 questions

    SQL Database Management

    CongenialCopernicium avatar
    CongenialCopernicium
    SQL Commands Overview
    14 questions

    SQL Commands Overview

    ExquisitePalladium avatar
    ExquisitePalladium
    SQL Skills Assessment - Data Manipulation
    60 questions

    SQL Skills Assessment - Data Manipulation

    AmicableWave avatar
    AmicableWave
    Data Definition Language (DDL) Overview
    26 questions

    Data Definition Language (DDL) Overview

    DeliciousSavanna avatar
    DeliciousSavanna
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser