SQLServer基础查询练习(附标准答案)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--创建数据库
USE[master]
GO
CREATE DATABASE[test]ON PRIMARY
(NAME=N'test',FILENAME=N'D:\SQL \DataBase\test.mdf',SIZE=3072KB, MAXSIZE=UNLIMITED,FILEGROWTH= 1024KB)
LOG ON
(NAME=N'test_log',FILENAME=N'D:\SQL \DataBase\test_log.ldf',SIZE = 1024KB,MAXSIZE= 2048GB,FILEGROWTH= 10%)
GO
ALTER DATABASE[test]SET COMPATIBILITY_LEVEL= 90
GO
IF (1 =FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC[test].[dbo].[sp_fulltext_database]@action='enable'
end
GO
ALTER DATABASE[test]SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE[test]SET ANSI_NULLS OFF
GO
ALTER DATABASE[test]SET ANSI_PADDING OFF
GO
ALTER DATABASE[test]SET ANSI_WARNINGS OFF
GO
ALTER DATABASE[test]SET ARITHABORT OFF
GO
ALTER DATABASE[test]SET AUTO_CLOSE OFF
GO
ALTER DATABASE[test]SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE[test]SET AUTO_SHRINK OFF
GO
ALTER DATABASE[test]SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE[test]SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE[test]SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE[test]SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE[test]SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE[test]SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE[test]SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE[test]SET DISABLE_BROKER
GO
ALTER DATABASE[test]SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO
ALTER DATABASE[test]SET DATE_CORRELATION_OPTIMIZATION OFF GO
ALTER DATABASE[test]SET TRUSTWORTHY OFF
GO
ALTER DATABASE[test]SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE[test]SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE[test]SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE[test]SET READ_WRITE
ALTER DATABASE[test]SET RECOVERY SIMPLE
GO
ALTER DATABASE[test]SET MULTI_USER
GO
ALTER DATABASE[test]SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE[test]SET DB_CHAINING OFF
GO
--创建表
USE test
CREATE TABLE emp
(
EMPNO NUMERIC(5, 0)NOT NULL,
ENAME NVARCHAR(10),
JOB NVARCHAR(9),
MGR NUMERIC(5, 0),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2, 0),
)
CREATE TABLE dept
(
DEPTNO NUMERIC(2),
DNAME NVARCHAR(14),
LOC NVARCHAR(13),
)
--插入数据
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK', 7902,'2000-12-17', 800,NULL, 20); INSERT INTO EMP VALUES
(7499,'allen','SALESMAN', 7698,'2001-2-20', 1600, 300, 30); INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN', 7698,'2001-2-22', 1250, 500, 30); INSERT INTO EMP VALUES
(7566,'JONES','MANAGER', 7839,'2001-4-2', 2975,NULL, 20);