MySQL Overview and History
40 Questions
0 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 is the primary query language used by MS SQL Server?

  • PL/SQL
  • T-SQL (correct)
  • MySQL
  • Oracle SQL

    • Which company developed MySQL?

  • MySQL AB (correct)
  • Microsoft
  • IBM
  • Oracle

    • In which year did Sun Microsystems acquire MySQL AB?

  • 2006
  • 2005
  • 2008 (correct)
  • 2010

    • What type of database system is MS SQL Server classified as?

    <p>Relational Database Management System</p> Signup and view all the answers

    What is one feature of MySQL that emphasizes its performance capability?

    <p>High Performance</p> Signup and view all the answers

    Which version of MySQL was released for Windows in January 1998?

    <p>Version 3.23</p> Signup and view all the answers

    What is one of the primary advantages of MySQL that is highlighted?

    <p>Lowest Total Cost of Ownership</p> Signup and view all the answers

    Which of the following is NOT a platform supported by MySQL?

    <p>Chrome OS</p> Signup and view all the answers

    What happens when a NOT NULL constraint is applied to a column?

    <p>The column must contain a value for every record.</p> Signup and view all the answers

    In the SQL command to create a table, which of the following correctly sets a default value for the SALARY column?

    <p>SALARY DECIMAL(18,2) DEFAULT 5000.00</p> Signup and view all the answers

    What is the purpose of the PRIMARY KEY constraint in the SQL statement?

    <p>It guarantees that the values in the ID column are unique.</p> Signup and view all the answers

    How would you modify the SALARY column to add a NOT NULL constraint after the table has been created?

    <p>ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL(18, 2) NOT NULL;</p> Signup and view all the answers

    Which SQL command correctly drops a DEFAULT constraint from the SALARY column?

    <p>ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL(18, 2) DEFAULT NULL;</p> Signup and view all the answers

    Why would you use the DEFAULT constraint in a table?

    <p>To automatically populate a column when no value is provided.</p> Signup and view all the answers

    What command should be used if you want to add a DEFAULT constraint to an already existing column?

    <p>ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL(18,2) DEFAULT 5000.00;</p> Signup and view all the answers

    What data type is used for the NAME column in the CUSTOMERS table?

    <p>VARCHAR(20)</p> Signup and view all the answers

    What does the SQL USE statement accomplish?

    <p>It selects an existing database.</p> Signup and view all the answers

    What is a correct syntax for creating a new table in SQL?

    <p>CREATE TABLE table_name(column1 datatype, column2 datatype);</p> Signup and view all the answers

    Which of the following components is NOT part of the CREATE TABLE statement?

    <p>Database name</p> Signup and view all the answers

    What does the NOT NULL constraint signify in a CREATE TABLE statement?

    <p>The column cannot be left empty.</p> Signup and view all the answers

    What will happen if the DROP TABLE statement is executed correctly?

    <p>The table along with its data and definitions will be permanently deleted.</p> Signup and view all the answers

    Which statement correctly describes the ALTER TABLE command?

    <p>It can add, delete, or modify columns in an existing table.</p> Signup and view all the answers

    In the context of the CUSTOMERS table example, which data type is used for the NAME column?

    <p>VARCHAR(20)</p> Signup and view all the answers

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

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

    What SQL command is used to add a foreign key constraint to an existing table?

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

    Which of the following is true about the CHECK constraint in SQL?

    <p>It ensures that values meet specific criteria before being accepted.</p> Signup and view all the answers

    What will happen if a record violates a CHECK constraint when being inserted into a table?

    <p>The record will not be inserted into the table.</p> Signup and view all the answers

    Which statement correctly drops a foreign key constraint?

    <p>ALTER TABLE ORDERS DROP FOREIGN KEY;</p> Signup and view all the answers

    In the CUSTOMERS table, which column has a CHECK constraint applied?

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

    Which of the following best describes the purpose of the PRIMARY KEY in the CUSTOMERS table?

    <p>It establishes a unique identifier for each customer record.</p> Signup and view all the answers

    What data type is used for the SALARY column in the CUSTOMERS table?

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

    What data type is used for the DATE column in the ORDERS table?

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

    What SQL command is used to ensure that the age of customers is at least 18?

    <p>ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint CHECK(AGE &gt;= 18);</p> Signup and view all the answers

    Which syntax is valid for dropping a CHECK constraint in a SQL table?

    <p>ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint;</p> Signup and view all the answers

    In the INSERT INTO statement, when may you omit the column names?

    <p>When inserting values for all columns in the table.</p> Signup and view all the answers

    How do you add multiple records to the CUSTOMERS table in one command?

    <p>Use a single INSERT INTO statement with multiple sets of values.</p> Signup and view all the answers

    What must be true about the values being inserted into a table?

    <p>The data types of the values must match the column types.</p> Signup and view all the answers

    What will happen if you try to insert a record with an AGE value less than 18 after the CHECK constraint is applied?

    <p>An error will occur, preventing the record from being inserted.</p> Signup and view all the answers

    If you want to ensure that each customer in the CUSTOMERS table has a unique ID, which constraint should be applied?

    <p>UNIQUE constraint</p> Signup and view all the answers

    What is the purpose of using the NOT NULL constraint in the context of the AGE field of the CUSTOMERS table?

    <p>To ensure that AGE must always be provided.</p> Signup and view all the answers

    Study Notes

    MySQL

    • Developed by MySQL AB, a Swedish company
    • Pronounced "my ess-que-ell"
    • Supports various platforms including Microsoft Windows, Linux distributions, UNIX, and Mac OS X
    • Available in free and paid versions
    • Offers a fast, multi-threaded, multi-user, and robust SQL database server

    MySQL History

    • Development began in 1994 by Michael Widenius and David Axmark
    • First internal release: 23rd May 1995
    • Windows version released: 8th January 1998 for Windows 95 and NT
    • Version 3.23: beta in June 2000, production release in January 2001
    • Version 4.0: beta in August 2002, production release in March 2003
    • Version 4.1: beta in June 2004, production release in October 2004
    • Version 5.0: beta in March 2005, production release in October 2005
    • Sun Microsystems acquired MySQL AB on 26th February 2008
    • Version 5.1: production release on 27th November 2008

    MySQL Features

    • High performance
    • High availability
    • Scalability and flexibility
    • Runs various applications
    • Robust transactional support
    • Strengths in web and data warehousing
    • Strong data protection
    • Comprehensive application development
    • Management ease
    • Open source freedom and 24/7 support
    • Lowest total cost of ownership

    MS SQL Server

    • A Relational Database Management System (RDBMS) developed by Microsoft Inc.
    • Primary query languages are T-SQL and ANSI SQL

    MS SQL Server History

    • 1987: Sybase releases SQL Server for UNIX
    • 1988: Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2
    • 1989: Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2
    • 1990: SQL Server 1.1 released with support for Windows 3.0 clients
    • 2000: Microsoft releases SQL Server 2000
    • 2001: Microsoft releases XML for SQL Server Web Release 1
    • 2002: Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server)
    • 2002: Microsoft releases SQLXML 3.0

    SQL USE Statement

    • Used to choose an existing database in the SQL schema
    • Syntax: USE DatabaseName;
    • Example: USE testDB;

    SQL CREATE TABLE Statement

    • Creates a new table
    • Syntax: 
      CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    column3 datatype,.....columnN datatype,
    PRIMARY KEY( one or more columns )
);
    • Key points:
      • CREATE TABLE is the keyword
      • table_name is the unique identifier for the table
      • column1, column2, column3...columnN define the table's columns and their data types
      • The PRIMARY KEY constraint is used to define a unique identifier for each record in the table

    Create Table Example

    • Creates a CUSTOMERS table with the following columns: ID, NAME, AGE, ADDRESS, and SALARY
    • The PRIMARY KEY constraint is set on the ID column
    • NOT NULL constraints are used to ensure that the columns ID, NAME and AGE do not contain null values
    CREATE TABLE CUSTOMERS(
     ID    INT                      NOT NULL,
     NAME VARCHAR (20)              NOT NULL,
     AGE   INT                      NOT NULL,
     ADDRESS     CHAR (25) ,
     SALARY      DECIMAL (18, 2),
     PRIMARY KEY (ID)
);

    SQL DROP TABLE Statement

    • Removes a table definition and all data associated with the table
    • NOTE: This command permanently deletes the table and its data
    • Syntax: DROP TABLE table_name;
    • Example: DROP TABLE CUSTOMERS;

    SQL ALTER TABLE Statement

    • Modifies an existing table
      • Adds, deletes, or modifies columns
      • Adds or removes constraints
    • Example of adding a NOT NULL constraint to the SALARY column of the CUSTOMERS table: 
    ALTER TABLE CUSTOMERS
            MODIFY SALARY      DECIMAL (18, 2) NOT NULL;

    DEFAULT Constraint

    • Specifies a default value for the column, used when no value is specified in the INSERT INTO command
    • Example: 
      CREATE TABLE CUSTOMERS(
   ID    INT                   NOT NULL,
   NAME VARCHAR (20)           NOT NULL,
   AGE   INT                   NOT NULL,
   ADDRESS     CHAR (25) ,
   SALARY      DECIMAL (18, 2) DEFAULT 5000.00,
   PRIMARY KEY (ID)
);
    • This creates the CUSTOMERS table with a default value of 5000.00 for the SALARY column
    • To add a DEFAULT constraint to an existing table:
    ALTER TABLE CUSTOMERS
       MODIFY SALARY       DECIMAL (18, 2) DEFAULT 5000.00;
    • To drop a DEFAULT constraint:
    ALTER TABLE CUSTOMERS
     ALTER COLUMN SALARY DROP DEFAULT;

    FOREIGN KEY Constraint

    • Ensures data integrity by establishing a link between two tables
    • References the PRIMARY KEY of another table
    • Example of creating ORDERS and CUSTOMERS tables and adding a FOREIGN KEY to the ORDERS table:
    CREATE TABLE CUSTOMERS(
     ID    INT                       NOT NULL,
     NAME VARCHAR (20)               NOT NULL,
     AGE   INT                       NOT NULL,
     ADDRESS     CHAR (25) ,
     SALARY      DECIMAL (18, 2),
     PRIMARY KEY (ID)
 );

 CREATE TABLE ORDERS (
     ID              INT           NOT NULL,
     DATE            DATETIME,
     CUSTOMER_ID INT references CUSTOMERS(ID),
     AMOUNT          double,
     PRIMARY KEY (ID)
 );
    • The CUSTOMER_ID column in the ORDERS table is a FOREIGN KEY that references the ID column in the CUSTOMERS table.
    • To add a FOREIGN KEY to an existing table:
    ALTER TABLE ORDERS
     ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
    • To drop a FOREIGN KEY constraint:
    ALTER TABLE ORDERS
     DROP FOREIGN KEY Customer_ID;

    CHECK Constraint

    • Enforces data validation by ensuring all values meet a specific condition
    • Example:
    CREATE TABLE CUSTOMERS(
     ID    INT                 NOT NULL,
     NAME VARCHAR (20)         NOT NULL,
     AGE   INT                 NOT NULL CHECK (AGE >= 18),
     ADDRESS     CHAR (25) ,
     SALARY      DECIMAL (18, 2),
     PRIMARY KEY (ID)
 );
    • This creates the CUSTOMERS table with a CHECK constraint on the AGE column that ensures all ages are 18 years or older.
    • To add a CHECK constraint to an existing table:
    ALTER TABLE CUSTOMERS
     ADD CONSTRAINT myCheckConstraint CHECK (AGE >= 18);
    • To drop a CHECK constraint:
    ALTER TABLE CUSTOMERS
     DROP CONSTRAINT myCheckConstraint;

    SQL INSERT Query

    • Adds new rows of data to a table

    • Syntax:

      INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

    • You can omit the column names if values are provided for all columns, ensuring the order matches the table’s column order

    • Example of inserting records into the CUSTOMERS table using the first syntax where specific columns are listed:

     INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

 INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
 VALUES (6, 'Komal', 22, 'MP', 4500.00 );
    • Example of inserting records into the CUSTOMERS table using the second syntax, where all columns are included in the order that they appear in the table:
     INSERT INTO CUSTOMERS VALUES ( 7, 'John' , 30, 'New York', 5000.00 );
 INSERT INTO CUSTOMERS VALUES ( 8, 'Mary' , 28, 'Los Angeles', 6000.00 );

    Studying That Suits You

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

    Quiz Team

    Related Documents

    Module 2 - Database Administration PDF

    Description

    This quiz covers the essential features and historical development of MySQL, a leading SQL database server. You'll learn about its origins, key versions, and performance characteristics. Test your knowledge of MySQL's evolution and capabilities.

    More Like This

    Inserting Data into MySQL Database with Python
    6 questions

    Inserting Data into MySQL Database with Python

    IntelligentGuitar avatar
    IntelligentGuitar
    MySQL và SQL
    5 questions

    MySQL và SQL

    EffectiveAltoSaxophone avatar
    EffectiveAltoSaxophone
    Diferencias y similitudes entre MySQL y SQL Server
    10 questions

    Diferencias entre SQL y MySQL - Prueba tus conocimientos con este quiz

    LighterTheme9174 avatar
    LighterTheme9174
    Connecting to Databases with MySQL
    21 questions

    Connecting to Databases with MySQL

    HighQualityTimpani5591 avatar
    HighQualityTimpani5591
    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser