Skip to main content
Engineering LibreTexts

9.5: Few Final Tidbits

  • Page ID
  • \( \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}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    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.

    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] ‘)


    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.

    ALTER TABLE  tblHotel

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

    ColumnName         int    IDENTITY(seed, increment)


    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




    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
      EMP_NUM CHAR(3)
      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

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

    • Was this article helpful?