Introducing
Your new presentation assistant.
Refine, enhance, and tailor your content, source relevant images, and edit visuals quicker than ever before.
Trending searches
Database Admin Week 4
DataBase Administration
Week 4
Understanding Combined Queries
There are basically two scenarios in which you’d use combined queries:
- To return similarly structured data from different tables in a single query
- To perform multiple queries against a single table, returning the data as one query
Combining Queries
Creating Combined Queries
Using UNION
Using UNION is simple enough. All you do is specify each SELECT statement and place the keyword UNION between each.
Creating Combined Queries
Full-Text Searching
Full-Text Searching
When data has been indexed, full-text searches may be performed using two predicates:
FREETEXT performs simple searches, matching by meaning as opposed to an exact text match.
CONTAINS performs searches for words or phrases, taking into account proximity, derived words, and synonyms.
Both FREETEXT and CONTAINS are used in SELECT statement WHERE clauses.
Data Insertion
Inserting Data
As its name suggests, INSERT is used to insert (add) rows to a database table. INSERT can be used in several ways:
- To insert a single complete row
- To insert a single partial row
- To insert multiple rows
- To insert the results of a query
You’ll now look at examples of each of these.
Inserting Complete Rows
Inserting Complete Rows
INSERT INTO Customers
VALUES(10006, 'Pep E. LaPew', '100 Main Street','Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
proper method
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
INSERT inserts a single row into a table. But what if you need to insert multiple rows? The basic INSERT statement only inserts a single row at a time, so you would need to use multiple INSERT statements. You could possibly submit them all at once, each terminated by a semicolon, like this:
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA');
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('M. Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA');
Updating Data
Updating and Deleting Data
To update (modify) data in a table, you use the UPDATE statement. UPDATE can be used in two ways:
- To update specific rows in a table
- To update all rows in a table
The UPDATE statement is very easy to use—some would say too easy. The basic format of an UPDATE statement is made up of three parts:
- The table to be updated
- The column names and their new values
- The filter condition that determines which rows should be updated
Deleting Data
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
SET cust_email = 'elmer@fudd.com'
Updating multiple columns requires a slightly different syntax:
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
To delete a column’s value, you can set it to NULL (assuming the table is defined to allow NULL values). You can do this as follows:
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
Here, the NULL keyword is used to save no value to the cust_email column.
Deleting Data
To delete (remove) data from a table, you use the DELETE statement. DELETE can be used in two ways:
- To delete specific rows from a table
- To delete all rows from a table
I already stated that UPDATE is very easy to use. The good (and bad) news is that DELETE is even easier to use.
The following statement deletes a single row from the customers table:
DELETE FROM customers
WHERE cust_id = 10006;
Creating Tables
T-SQL statements are not used just for table data manipulation. Indeed, T-SQL can be used to perform all database and table operations, including the creation and manipulation of tables themselves.
There are generally two ways to create database tables:
- Using an administration tool (like Microsoft SQL Server Management Studio) to create and manage database tables interactively
- Using T-SQL statements to manipulate tables directly
Creating and Manipulating Tables
Basic Table Creation
To create a table using CREATE TABLE, you must specify the following information:
- The name of the new table, after the keywords CREATE TABLE
- The name and definition of the table columns, separated by commas
Basic Table Creation
Table Creation
CREATE TABLE customers
(
cust_id INT NOT NULL IDENTITY(1,1),
cust_name NCHAR(50) NOT NULL ,
cust_address NCHAR(50) NULL ,
cust_city NCHAR(50) NULL ,
cust_state NCHAR(5) NULL ,
cust_zip NCHAR(10) NULL ,
cust_country NCHAR(50) NULL ,
cust_contact NCHAR(50) NULL ,
cust_email NCHAR(255) NULL ,
PRIMARY KEY (cust_id)
);
Updating Tables
To change a table using ALTER TABLE, you must specify the following information:
- The name of the table to be altered after the keywords ALTER TABLE. (The table must exist, or an error is generated.)
- The list of changes to be made.
Updating Tables
The following example adds a column to a table:
ALTER TABLE vendors
ADD vend_phone CHAR(20);
This statement adds a column named vend_phone to the vendors table. The datatype must be specified.
To remove this newly added column, you can do the following:
ALTER TABLE vendors
DROP COLUMN vend_phone;
One common use for ALTER TABLE is to define foreign keys. The following is the code used to define the foreign keys used by the tables in this book:
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num)
REFERENCES orders (order_num);
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id)
REFERENCES vendors (vend_id);
ALTER TABLE productnotes
ADD CONSTRAINT fk_productnotes_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);
Complex table structure changes may require a manual move process involving these steps:
1. Create a new table with the new column layout.
2. Use the INSERT SELECT or SELECT INTO statement (see Lesson 18, “Inserting Data,” for details of this statement) to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.
3. Verify that the new table contains the desired data.
4. Rename the old table (or delete it, if you are really brave).
5. Rename the new table with the name previously used by the old table.
6. Re-create any triggers, stored procedures, indexes, and foreign keys as needed.
Deleting Tables
Deleting tables (actually removing the entire table, not just the contents) is very easy—arguably too easy. Tables are deleted using the DROP TABLE statement:
DROP TABLE customers2;
Deleting Tables
Renaming Tables
There is no T-SQL statement for renaming tables, but a SQL Server–provided stored procedure named sp_rename can be used to accomplish this:
EXEC sp_rename 'customers2', 'customers';
Renaming Tables
Overview of SQL Server Security
Ownership and User-Schema Separation in SQL Server
Authentication
Authorization
accounting