basequery.sql

关于VB模块的

use pubs
select * from authors
select au_id,au_lname,au_fname,phone,address,city,state,zip from authors

CREATE TABLE students(student_id  smallint      IDENTITY(1,1)      PRIMARY KEY CLUSTERED,student_desc varchar(50)     NOT NUll      Default 'New Position - title not formalized yet',min_lvl tinyint NOT NULL     CHECK (min_lvl >= 10),max_lvl tinyint NOT NULL   CHECK (max_lvl <= 250) )
drop table students

select * from students

SELECT au_lname + ', ' + au_fName		
 AS authorname,au_id,phone,address,city,state,zip,contract
FROM authors
ORDER BY au_lname, au_fname ASC		

SELECT a.au_lname, a.au_fname, t.title,a.phone,a.address,a.city,a.state,a.zip		
FROM authors a INNER JOIN titleauthor ta		
   ON a.au_id = ta.au_id JOIN titles t			
   ON ta.title_id = t.title_id					
WHERE t.type = 'trad_cook'					
ORDER BY t.title ASC				


SELECT au_lname, au_fname,phone,address,city,state,zip,contract	
FROM authors							
WHERE au_id IN				
   (SELECT au_id
   FROM titleauthor
   WHERE royaltyper < 50)


SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,type,advance,notes,pubdate,
price-(SELECT AVG(price) FROM titles) AS difference
FROM titles
WHERE type='popular_comp'

select * from authors

CREATE TABLE new_authors
(
 au_id    id,
 au_lname varchar(40),
 au_fname varchar(20),
 phone    char(12),
 address  varchar(40),
 city     varchar(20),    
 state    char(2), 
 zip         char(5),
 contract bit
)

drop table new_authors
select * from new_authors
insert new_authors values ('172-32-1177','asd','asd','234','sadf','sda','f','1234','1')

insert new_authors values ('123','liu','tao','1234','asdf','asdf','asdf','2134','1')

CREATE TABLE new_authors ( au_id    varchar(40), au_lname varchar(40), au_fname varchar(20), phone    varchar(40), address  varchar(40), city     varchar(20), state    varchar(40), zip         varchar(40), contract varchar(40))


UPDATE new_authors
SET state = 'ZZ' 
FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1
WHERE authors.au_id = t1.au_id


UPDATE authors
    SET authors.au_fname = 'LIU'
    WHERE au_fname = 'TAO'
insert new_authors values ('172-32-1177','Liu','Tao','234','sadf','sda','f','1234','1')

select * from new_authors

CREATE DATABASE Sales
ON 
( NAME = Sales_dat,              
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',           
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )

use sales
use pubs

drop database sales

select * from titleview

CREATE VIEW accounts			
 (title, advance, amt_due)		
WITH ENCRYPTION				
AS 
SELECT title, advance, price * royalty * ytd_sales	
FROM titles						
WHERE price > $5	

select * from accounts	

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'accounts'				

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'accounts')  DROP VIEW accounts




CREATE PROCEDURE au_info 	
   @lastname varchar(40), 	
   @firstname varchar(20) 
AS 
SELECT au_lname, au_fname, title, pub_name	
   FROM authors a INNER JOIN titleauthor ta	
      ON a.au_id = ta.au_id INNER JOIN titles t	
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
   WHERE  au_fname = @firstname			
      AND au_lname = @lastname


IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'au_info' AND type = 'P')
   DROP PROCEDURE au_info

EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'



CREATE TRIGGER employee_trigger
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
SELECT @min_lvl = min_lvl, 
   @max_lvl = max_lvl, 
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
   JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
END

select * from employee
insert employee values ('PMA42628M','Paolo','M','Accorti','13','35','0877','2001-1-1')


delete from employee where emp_id = 'PMA42628M'
insert employee values ('PMA42628M','Paolo','M','Accorti','1','213','0877','2001-1-1')

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_trigger' AND type = 'TR')  DROP TRIGGER employee_trigger