Which of the following statements will modify the data type of Sid column in enrolled table note there is no foreign key relationship between tables student and enrolled?
Adrienne Watt & Nelson Eng Show
The SQL data manipulation language (DML) is used to query and modify database data. In this chapter, we will describe how to use the SELECT, INSERT, UPDATE, and DELETE SQL DML command statements, defined below.
In the SQL DML statement:
The SELECT statement, or command, allows the user to extract data from tables, based on specific criteria. It is processed according to the following sequence: SELECT DISTINCT item(s) We can use the SELECT statement to generate an employee phone list from the Employees table as follows: SELECT FirstName, LastName, phone This action will display employee’s last name, first name, and phone number from the Employees table, seen in Table 16.1.
Table 16.1. Employees table. In this next example, we will use a Publishers table (Table 16.2). (You will notice that Canada is mispelled in the Publisher Country field for Example Publishing and ABC Publishing. To correct mispelling, use the UPDATE statement to standardize the country field to Canada – see UPDATE statement later in this chapter.)
Table 16.2. Publishers table. If you add the publisher’s name and city, you would use the SELECT statement followed by the fields name separated by a comma: SELECT PubName, city This action will display the publisher’s name and city from the Publishers table. If you just want the publisher’s name under the display name city, you would use the SELECT statement with no comma separating pub_name and city: SELECT PubName city Performing this action will display only the pub_name from the Publishers table with a “city” heading. If you do not include the comma, SQL Server assumes you want a new column name for pub_name. SELECT statement with WHERE criteriaSometimes you might want to focus on a portion of the Publishers table, such as only publishers that are in Vancouver. In this situation, you would use the SELECT statement with the WHERE criterion, i.e., WHERE city = ‘Vancouver’. These first two examples illustrate how to limit record selection with the WHERE criterion using BETWEEN. Each of these examples give the same results for store items with between 20 and 50 items in stock. Example #1 uses the quantity, qty BETWEEN 20 and 50. SELECT StorID, qty, TitleID Example #2, on the other hand, uses qty >=20 and qty <=50 . SELECT StorID, qty, TitleID Example #3 illustrates how to limit record selection with the WHERE criterion using NOT BETWEEN. SELECT StorID, qty, TitleID The next two examples show two different ways to limit record selection with the WHERE criterion using IN, with each yielding the same results. Example #4 shows how to select records using province= as part of the WHERE statement. SELECT * Example #5 select records using province IN as part of the WHERE statement. SELECT * The final two examples illustrate how NULL and NOT NULL can be used to select records. For these examples, a Books table (not shown) would be used that contains fields called Title, Quantity, and Price (of book). Each publisher has a Books table that lists all of its books. Example #6 uses NULL. SELECT price, title Example #7 uses NOT NULL. SELECT price, title Using wildcards in the LIKE clauseThe LIKE keyword selects rows containing fields that match specified portions of character strings. LIKE is used with char, varchar, text, datetime and smalldatetime data. A wildcard allows the user to match fields that contain certain letters. For example, the wildcard province = ‘N%’ would give all provinces that start with the letter ‘N’. Table 16.3 shows four ways to specify wildcards in the SELECT statement in regular express format.
Table 16.3. How to specify wildcards in the SELECT statement. In example #1, LIKE ‘Mc%’ searches for all last names that begin with the letters “Mc” (e.g., McBadden). SELECT LastName For example #2: LIKE ‘%inger’ searches for all last names that end with the letters “inger” (e.g., Ringer, Stringer). SELECT LastName In, example #3: LIKE ‘%en%’ searches for all last names that have the letters “en” (e.g., Bennett, Green, McBadden). SELECT LastName SELECT statement with ORDER BY clauseYou use the ORDER BY clause to sort the records in the resulting list. Use ASC to sort the results in ascending order and DESC to sort the results in descending order. For example, with ASC: SELECT * And with DESC: SELECT * SELECT statement with GROUP BY clauseThe GROUP BY clause is used to create one output row per each group and produces summary values for the selected columns, as shown below. SELECT type Here is an example using the above statement. SELECT type
AS ‘Type’, MIN(price) AS ‘Minimum Price’ If the SELECT statement includes a WHERE criterion where price is not null, SELECT type, price then a statement with the GROUP BY clause would look like this: SELECT type AS ‘Type’, MIN(price) AS ‘Minimum Price’ Using COUNT with GROUP BYWe can use COUNT to tally how many items are in a container. However, if we want to count different items into separate groups, such as marbles of varying colours, then we would use the COUNT function with the GROUP BY command. The below SELECT statement illustrates how to count groups of data using the COUNT function with the GROUP BY clause. SELECT COUNT(*) Using AVG and SUM with GROUP BYWe can use the AVG function to give us the average of any group, and SUM to give the total. Example #1 uses the AVG FUNCTION with the GROUP BY type. SELECT AVG(qty) Example #2 uses the SUM function with the GROUP BY type. SELECT SUM(qty) Example #3 uses both the AVG and SUM functions with the GROUP BY type in the SELECT statement. SELECT ‘Total Sales’ = SUM(qty), ‘Average Sales’ = AVG(qty), stor_id Restricting rows with HAVINGThe HAVING clause can be used to restrict rows. It is similar to the WHERE condition except HAVING can include the aggregate function; the WHERE cannot do this. The HAVING clause behaves like the WHERE clause, but is applicable to groups. In this example, we use the HAVING clause to exclude the groups with the province ‘BC’. SELECT au_fname AS ‘Author”s First Name’, province as ‘Province’ INSERT statementThe INSERT statement adds rows to a table. In addition,
The syntax for the INSERT statement is: INSERT [INTO] Table_name | view name [column_list] When inserting rows with the INSERT statement, these rules apply:
When you specify values for only some of the columns in the column_list, one of three things can happen to the columns that have no values:
This example uses INSERT to add a record to the publisher’s Authors table. INSERT INTO Authors This following example illustrates how to insert a partial row into the Publishers table with a column list. The country column had a default value of Canada so it does not require that you include it in your values. INSERT INTO Publishers (PubID, PubName,
city, province) To insert rows into a table with an IDENTITY column, follow the below example. Do not supply the value for the IDENTITY nor the name of the column in the column list. INSERT INTO jobs Inserting specific values into an IDENTITY columnBy default, data cannot be inserted directly into an IDENTITY column; however, if a row is accidentally deleted, or there are gaps in the IDENTITY column values, you can insert a row and specify the IDENTITY column value. IDENTITY_INSERT option To allow an insert with a specific identity value, the IDENTITY_INSERT option can be used as follows. SET IDENTITY_INSERT jobs ON Inserting rows with a SELECT statementWe can sometimes create a small temporary table from a large table. For this, we can insert rows with a SELECT statement. When using this command, there is no validation for uniqueness. Consequently, there may be many rows with the same pub_id in the example below. This example creates a smaller temporary Publishers table using the CREATE TABLE statement. Then the INSERT with a SELECT statement is used to add records to this temporary Publishers table from the publis table. CREATE TABLE dbo.tmpPublishers ( In this example, we’re copying a subset of data. INSERT tmpPublishers (pub_id, pub_name) In this example, the publishers’ data are copied to the tmpPublishers table and the country column is set to Canada. INSERT
tmpPublishers (PubID, PubName, city, province, country) UPDATE statementThe UPDATE statement changes data in existing rows either by adding new data or modifying existing data. This example uses the UPDATE statement to standardize the country field to be Canada for all records in the Publishers table. UPDATE Publishers This example increases the royalty amount by 10% for those royalty amounts between 10 and 20. UPDATE roysched Including subqueries in an UPDATE statementThe employees from the Employees table who were hired by the publisher in 2010 are given a promotion to the highest job level for their job type. This is what the UPDATE statement would look like. UPDATE Employees DELETE statementThe DELETE statement removes rows from a record set. DELETE names the table or view that holds the rows that will be deleted and only one table or row may be listed at a time. WHERE is a standard WHERE clause that limits the deletion to select records. The DELETE syntax looks like this. DELETE [FROM] {table_name | view_name } The rules for the DELETE statement are:
What follows are three different DELETE statements that can be used. 1. Deleting all rows from a table. DELETE 2. Deleting selected rows: DELETE 3. Deleting rows based on a value in a subquery: DELETE FROM Sales There are many built-in functions in SQL Server such as:
Below you will find detailed descriptions and examples for the first four functions. Aggregate functionsAggregate functions perform a calculation on a set of values and return a single, or summary, value. Table 16.4 lists these functions.
Table 16.4 A list of aggregate functions and descriptions. Below are examples of each of the aggregate functions listed in Table 16.4. Example #1: AVG SELECT AVG (price) AS ‘Average Title Price’ Example #2: COUNT SELECT COUNT(PubID) AS ‘Number of Publishers’ Example #3: COUNT SELECT COUNT(province) AS ‘Number of
Publishers’ Example #3: COUNT (*) SELECT COUNT(*) Example #4: MAX SELECT MAX (HireDate) Example #5: MIN SELECT MIN (price) Example #6: SUM SELECT SUM(discount) AS ‘Total Discounts’ Conversion functionThe conversion function transforms one data type to another. In the example below, a price that contains two 9s is converted into five characters. The syntax for this statement is SELECT ‘The date is ‘ + CONVERT(varchar(12), getdate()). SELECT CONVERT(int, 10.6496) In this second example, the conversion function changes data to a data type with a different size. SELECT title_id, CONVERT(char(4), ytd_sales) as
‘Sales’ Date functionThe date function produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts. If the date parameter is a smalldatetime value, the result is also a smalldatetime value. The DATEADD function is used to add and increment date values. The syntax for this function is DATEADD(datepart, number, date). SELECT
DATEADD(day, 3, hire_date) In this example, the function DATEDIFF(datepart, date1, date2) is used. This command returns the number of datepart “boundaries” crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all data types such as minutes, seconds, and milliseconds. SELECT DATEDIFF(day, HireDate, ‘Nov 30 1995’) For any particular date, we can examine any part of that date from the year to the millisecond. The date parts (DATEPART) and abbreviations recognized by SQL Server, and the acceptable values are listed in Table 16.5.
Table 16.5. Date part abbreviations and values. Mathematical functionsMathematical functions perform operations on numeric data. The following example lists the current price for each book sold by the publisher and what they would be if all prices increased by 10%. SELECT Price, (price * 1.1) AS ‘New Price’, title Joining two or more tables is the process of comparing the data in specified columns and using the comparison results to form a new table from the rows that qualify. A join statement:
Although the comparison is usually for equality – values that match exactly – other types of joins can also be specified. All the different joins such as inner, left (outer), right (outer), and cross join will be described below. Inner joinAn inner join connects two tables on a column with the same data type. Only the rows where the column values match are returned; unmatched rows are discarded. Example #1 SELECT jobs.job_id, job_desc Example #2 SELECT authors.au_fname, authors.au_lname, books.royalty, title Left outer joinA left outer join specifies that all left outer rows be returned. All rows from the left table that did not meet the condition specified are included in the results set, and output columns from the other table are set to NULL. This first example uses the new syntax for a left outer join. SELECT publishers.pub_name, books.title This is an example of a left outer join using the old syntax. SELECT publishers.pub_name, books.title Right outer joinA right outer join includes, in its result set, all rows from the right table that did not meet the condition specified. Output columns that correspond to the other table are set to NULL. Below is an example using the new syntax for a right outer join. SELECT titleauthor.title_id, authors.au_lname, authors.au_fname This second example show the old syntax used for a right outer join. SELECT titleauthor.title_id, authors.au_lname, authors.au_fname Full outer joinA full outer join specifies that if a row from either table does not match the selection criteria, the row is included in the result set, and its output columns that correspond to the other table are set to NULL. Here is an example of a full outer join. SELECT books.title, publishers.pub_name, publishers.province Cross joinA cross join is a product combining two tables. This join returns the same rows as if no WHERE clause were specified. For example: SELECT au_lname, pub_name, Key Termsaggregate function: returns summary values ASC: ascending order conversion function: transforms one data type to another cross join: a product combining two tables date function: displays information about dates and times DELETE statement: removes rows from a record set DESC: descending order full outer join: specifies that if a row from either table does not match the selection criteria GROUP BY: used to create one output row per each group and produces summary values for the selected columns inner join: connects two tables on a column with the same data type INSERT statement: adds rows to a table left outer join: specifies that all left outer rows be returned mathematical function: performs operations on numeric data right outer join: includes all rows from the right table that did not meet the condition specified SELECT statement: used to query data in the database string function: performs operations on character strings, binary data or expressions system function: returns a special piece of information from the database text and image functions: performs operations on text and image data UPDATE statement: changes data in existing rows either by adding new data or modifying existing data wildcard: allows the user to match fields that contain certain letters. ExercisesFor questions 1 to 18 use the PUBS sample database created by Microsoft. To download the script to generate this database please go to the following site: http://www.microsoft.com/en-ca/download/details.aspx?id=23654.
Advanced Questions (Union, Intersect, and Minus)
Advanced Questions (Using Joins)
Which of the following statements will modify the data type of an already existing column?ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type; Alters the table by changing the datatype of column.
Which of the following queries will change the data type of an existing column phone to the varchar data type?Which of the following queries will change the data type of an existing column (phone) to the varchar data type? ALTER TABLE author ALTER COLUMN phone SET TYPE VARCHAR(20);
Which of the following SQL command you would use to list the columns with data types of a table?You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
Which of the following statement will insert the record into the student table?The INSERT INTO statement is used to insert new records in a table.
|