【SQL开源代码栏目提醒】:网学会员,鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“basequery.sql”一文,供大家参考学习!
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\ms
sql\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