MsSQL(2): 예제

CREATE DATABASE shopdb;
USE shopdb;

SELECT DB_NAME() /* 데이터베이스 확인 */
SELECT @@SERVERNAME, @@SERVICENAME, DB_NAME() /* 서버 / 인스턴스 / 데이터베이스 확인 */

CREATE TABLE memberTBL(
	memberID CHAR(8) NOT NULL,  /* NUll  허용 안함*/
	memberName NCHAR(5) NOT NULL, /* NUll  허용*/
	memberAddress NCHAR(20) NULL
);

CREATE TABLE productTBL(
	productName NCHAR(4) NOT NULL,
	cost INT NOT NULL,
	makeDate DATE NULL,
	company NCHAR(5) NULL,
	amount INT NOT NULL
);


INSERT INTO memberTBL VALUES('Dang', '당탕이', '경기도 부천시 중동');
INSERT INTO memberTBL VALUES('Jee', '지운이', '서울 은평구 증산동');
INSERT INTO memberTBL VALUES('Han', '한주연', '인천 남구 주안동');
INSERT INTO memberTBL VALUES('Sang', '상달이', '경기 성남시 분당구');

INSERT INTO productTBL VALUES('컴퓨터',10, '2021-01-01' ,'삼성', 17);
INSERT INTO productTBL VALUES('세탁기',20, '2022-09-01' ,'LG', 3);
INSERT INTO productTBL VALUES('냉장고',5, '2023-02-01' ,'대우', 22);

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%productTBL%' /* 테이블 확인*/
SELECT * FROM SYSOBJECTS WHERE xtype = 'U' /* 테이블 확인*/



SELECT * FROM memberTBL;
SELECT * FROM productTBL;
SELECT memberName, memberAddress FROM memberTBL;
SELECT * FROM productTBL WHERE productName = '컴퓨터';
SELECT productName FROM productTBL WHERE amount >= 12;



USE shopdb;
DROP TABLE productTBL, memberTBL;
USE tempdb;
DROP DATABASE shopdb;