Skip to main content
Engineering LibreTexts

15.5: Few Final Tidbits

  • Page ID
    92208
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    User Defined Types

    User defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls.

    To create a user-defined data type in SQL Server, choose types under “Programmability” in your database. Next, right click and choose ‘New’ –>‘User-defined data type’ or execute the sp_addtype system stored procedure. After this, type:

    sp_addtype ssn, ‘varchar(11)’, ‘NOT NULL’

    This will add a new user-defined data type called SIN with nine characters.

    In this example, the field EmployeeSIN uses the user-defined data type SIN.

    CREATE TABLE SINTable
    (
    EmployeeID        INT Primary Key,
    EmployeeSIN       SIN,
    CONSTRAINT        CheckSIN
    CHECK (EmployeeSIN LIKE ‘ [0-9][0-9][0-9] – [0-9][0-9] [0-9] – [0-9][0-9][0-9] ‘)
    )

    ALTER TABLE

    You can use ALTER TABLE statements to add and drop constraints.

    • ALTER TABLE allows columns to be removed.
    • When a constraint is added, all existing data are verified for violations.

    In this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field.

    USE HOTEL
    GO
    ALTER TABLE  tblHotel
    ADD CONSTRAINT unqName UNIQUE (Name)

    Use the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName.

    ADD
    ColumnName         int    IDENTITY(seed, increment)

    DROP TABLE

    The DROP TABLE will remove a table from the database. Make sure you have the correct database selected.

    DROP TABLE tblHotel

    Executing the above SQL DROP TABLE statement will remove the table tblHotel from the database.

     

     

    Key Terms

    DDL: abbreviation for data definition language

    DML: abbreviation for data manipulation language

    SEQUEL: acronym for Structured English Query Language; designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R

    Structured Query Language (SQL): a database language designed for managing data held in a relational database management system

     

     

    Exercises

    1. Using the information for the Chapter 9 exercise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.
    2. Create the table shown here in SQL Server and show the statements you used.
      Table: Employee
      ATTRIBUTE (FIELD) NAME DATA DECLARATION
      EMP_NUM CHAR(3)
      EMP_LNAME VARCHAR(15)
      EMP_FNAME VARCHAR(15)
      EMP_INITIAL CHAR(1)
      EMP_HIREDATE DATE
      JOB_CODE CHAR(3)
      Table \(\PageIndex{1}\): Copy and Paste Caption here. (Copyright; author via source)
    3. Having created the table structure in question 2, write the SQL code to enter the rows for the table shown in Table \(\PageIndex{1}\).

     

    Ch15 Exercise Fig15.1
    Figure \(\PageIndex{2}\): Employee table with data. by A. Watt.

    Use Figure \(\PageIndex{2}\) to answer questions 4 to 10.

    1. Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value.
    2. Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job code of 502.
    3. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (Hint: Use logical operators to include all the information given in this problem.)
    4. Add the attributes EMP_PCT and PROJ_NUM to the Employee table. The EMP_PCT is the bonus percentage to be paid to each employee.
    5. Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.
    6. Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher.
    7. Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its original condition preceding this question.)

    Also see  Appendix C: SQL Lab with Solution


    15.5: Few Final Tidbits is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?