New

Long-Term Learning

Learn efficiently and remember over time.

Start Long-Term Learning

Get personalized study reminders at intervals optimized for better retention.
Track your progress on this set by creating a folder
Or add to an existing folder

Add this set to a folder

  • The vast majority of SQL statements are designed to ___

    retrieve user-requested information from a database through the use of queries.

    SELECT ???

    The SQL command for retrieving any data from a database

    There are only three things you need to be sure to identify in your statement to form a proper SELECT query: ___

    • Columns to retrieve
    • Tables to retrieve the columns from
    • Conditions, if any, that the data must satisfy

    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary >= 50,000

    you want to give your boss a list of employees whose salary is above $50,000 per year.

    You want to give your boss a list of employees whose salary is above $50,000 per year. You are interested in retrieving only those employees who fit that criteria. .. you could do this in SQL like this: ___

    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary >= 50,000

    To identify more than one column to include in your query, simply type each column name and separate the names with a ___

    comma.

    If you wish to choose all columns from within a table, you can do so by typing an ___

    asterisk (*) in the place where the column name(s) would otherwise be given.

    If you need to use a space in your statement, you need to enclose the words in ___

    square brackets or double quotes—for example, [first name] or "first name"

    You could select all available fields from one table simply by issuing the following command:

    SELECT * FROM employees

    The WHERE clause could be added to a query to find only those employees who work in the company's shipping department, as shown here:

    SELECT first_name, last_name
    FROM employees
    WHERE department = 'shipping'

    SELECT first_name, last_name
    FROM employees
    WHERE department = 'shipping' AND gender = 'F'

    ...this query uses the AND conjunction to yield the names of all employees who are in the shipping department and who are female.

    BETWEEN clause

    allows you to specify the range to be used in a "between x and y" query format.

    SELECT first_name, last_name
    FROM employees
    WHERE NOT department = 'shipping'

    you want a list of all employees who don't work in the shipping department.

    WHERE department <> 'shipping'

    it will produce the same results:

    WHERE NOT department = 'shipping'

    The UNION clause...

    allows you to combine the results of two or more queries into a resulting single set that includes all the rows belonging to the query in that union.

    The JOIN clause/statements

    allows you to combine related data from multiple table sources.

    EXCEPT clause gives you the ___

    final result set where data exists in the first query and not in the second dataset (returns any distinct values from the left query that are not also found on the right query)

    The INTERSECT gives you the ___

    final result set where values in both of the queries match by the query on both the left and right sides of the operand (returns any distinct values found on both the left and right sides of this operand)

    Basic rules apply to use of the EXCEPT, INTERSECT and UNION clauses:

    • The number and order of the columns must be the same in all queries.
    • The data types must be compatible.

    Retrieve one list showing products with work orders ?

    SELECT ProductID
    FROM Production.Product
    INTERSECT
    SELECT ProductID
    FROM Production.WorkOrder;

    List showing products without any work orders ?

    SELECT ProductID
    FROM Production.Product
    EXCEPT
    SELECT ProductID
    FROM Production.WorkOrder;

    JOIN statements can be specified in either the FROM or the WHERE clause, but it is recommended that you specify them in the ___

    FROM clause.

    There are three types of JOIN statements you should be aware of:

    • Inner joins
    • Outer joins
    • Cross joins

    Inner joins ???

    allow you to match related records taken from different source tables.

    Outer joins ???

    can include records from one or both tables you are querying that do not have any corresponding record(s) in the other table.

    Cross joins ???

    return all rows from one table along with all rows from the other table. WHERE conditions should always be included.

    There are three types of outer joins:

    LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

    self-join ???

    you may wish to have a table join with itself, say if you want to compare records from within the same table.

    To insert data, you can use ___

    SSMS or the INSERT statement.

    Insert data using SQL Server Management Studio:

    Launch the SSMS application -> connected to the database -> Databases folder -> Tables folder -> Right-click the table name and chose Edit Top 200 Rows -> Enter your data into the last row of the table

    INSERT INTO <table_name> (<columns>)
    VALUES (<values>)

    The <columns> clause would contain your ___

    comma-separated list of the column names in the table you wish to include

    INSERT INTO <table_name> (<columns>)
    VALUES (<values>)

    the <values> clause would contain the ___

    values you would like to insert.

    INSERT statement that adds two new employees to our employee table would appear as follows:

    INSERT INTO employee (first_name, last_name, employee_id, department) VALUES ('David', 'Clark', 610008, 'shipping'),
    ('Arnold', 'Davis', 610009, 'accounting')

    The function of the UPDATE statement is to ___

    change data in a table or view.

    INSERT INTO department (first_name, last_name, department_id) VALUES ('Doug', 'Able', 4)

    Add a record in the department table with our new supervisor's name and department ID information.

    Update our employee table to reflect any employees who do not have an assigned department supervisor ___

    UPDATE employee
    SET department = 4
    WHERE department IS NULL

    To update a table using the SSMS graphical interface, you simply need to follow these steps:

    • Open the SSMS interface.
    • Open the table in which you wish to update data.
    • Locate the row in which you wish to update the records within the Open Table view.

    There are several different ways to remove rows from a table or view:

    - You can identify and delete individual rows from the database using the DELETE syntax,
    - delete all the rows using a truncate table statement, or
    - remove the entire table using the drop table statement.

    You can use the DELETE statement to ___

    remove one or more rows in a table or view.

    DELETE statement is structured as follows:

    DELETE FROM <table_name>
    WHERE <conditions>

    You could delete all accounting department employees from a company's employee table if, because of a corporate takeover, they are no longer employed by the company:

    DELETE FROM employee
    WHERE department = 'accounting'

    The syntax of the TRUNCATE statement

    TRUNCATE TABLE <table_name>

    The TRUNCATE TABLE statement ___

    removes the actual data (delete all the rows) from within the table, but it leaves the table structure in place for future use.

    DROP TABLE statement

    DROP TABLE <table_name>
    Remove an entire table.

    Referential integrity does not allow deletion of tables unless ___

    ...all of the related tables are deleted using a cascading delete.

    One of the most common mistakes in database manipulation is the accidental loss of entire tables. The best way to avoid this type of situation in the first place is to ensure that your database uses...

    An entire table can be removed with the DROP TABLE command. The best way to avoid the accidental deletion of entire tables is to use ___

    Referential integrity

    Data is most commonly deleted, truncated, or accidentally updated during regular maintenance tasks, and one of the best ways to keep this from occurring is to use ___

    transactions when updating data.

    A sample transaction statement might appear as follows:

    BEGIN TRAN
    DELETE FROM <table_name>

    Many times mistakes occur through simple errors, and if you use the ___

    BEGIN TRAN and a COMMIT or ROLLBACK while performing maintenance tasks, you will catch most accidents before they happen.

    The NOT keyword is used to ___

    search data in terms of what you don't want in your output.

    Please allow access to your computer’s microphone to use Voice Recording.

    Having trouble? Click here for help.

    We can’t access your microphone!

    Click the icon above to update your browser permissions above and try again

    Example:

    Reload the page to try again!

    Reload

    Press Cmd-0 to reset your zoom

    Press Ctrl-0 to reset your zoom

    It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

    Please upgrade Flash or install Chrome
    to use Voice Recording.

    For more help, see our troubleshooting page.

    Your microphone is muted

    For help fixing this issue, see this FAQ.

    Star this term

    You can study starred terms together

    NEW! Voice Recording

    This is a Plus feature