Shulou(Shulou.com)05/31 Report--

This article will explain in detail about SQL Server database creation and table creation as well as how to operate the condition query. The content of the article is of high quality, so the editor will share it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The learning process of SQLServer database is relatively boring, because we should not only master the operation of enterprise manager, but also master the query of SQL statement. In fact, this process is skillfully mastered by doing a lot of practical practice. The following editor to explain the SQLServer database to create a database and create tables and conditional query how to operate?

How to operate SQLServer database creation database and table creation and conditional query

1. Create a database and create tables

CreatedatabaseCAPcreatetableCUSTOMERS (cidvarchar (10) primarykey,canmevarchar (10), cityvarchar (20), discntmoney) insertintoCUSTOMERSvalues ('c001) insertintoCUSTOMERSvalues (' c002) BasicsAccords (10), cityvarchar (20), discntmoney) insertintoCUSTOMERSvalues ('c004)

CreatetablePRODUCTS (pidvarchar (10) primarykey,pnamevarchar (10), cityvarchar (20), quantityvarchar (10), pricemoney) insertintoPRODUCTSvalues ("111400", "0.50") insertintoPRODUCTSvalues ("203000", "0.50") insertintoPRODUCTSvalues ("p03", "razor", "Duluthong,"150600", 1.00) insertintoPRODUCTSvalues ("p04"," pencil "," Duluthong "," 125300 "," 1.00 ") insertintoPRODUCTSvalues (" p05", "pencilot" and "Dallas"). '221400) insertintoPRODUCTSvalues (' p06grammar, foldershop, and dallaspen,) insertintoPRODUCTSvalues ('p07, respectively, caseology, and,

CreatetableAGENTS (aidvarchar (4) primarykey,anamevarchar (10), cityvarchar (10), [percent] int) insertintoAGENTSvalues (6) insertintoAGENTSvalues (7) insertintoAGENTSvalues (6) insertintoAGENTSvalues (5) insertintoAGENTSvalues (5)

CreatetableORDERS (ordnovarchar (4), [month] varchar (3), cidvarchar (10), aidvarchar (4), pidvarchar (10), qtyint,dollarsmoney) insertintoORDERSvalues ('1011 penny, p01, p01, p02, p02, and, () () insertintoORDERSvalues () insertintoORDERSvalues ("1012" and "p01", "p01"," p02", "400180.00" and "insertintoORDERSvalues". ) insertintoORDERSvalues ('1018 paraphernalia, p001', p03', p04', p04', 600540.00) insertintoORDERSvalues (' 1023', p001', p04', p04', p05, and 500450.00) insertintoORDERSvalues ('1022,000,05, and p06, 400720.00) insertintoORDERSvalues (' 1025,000,05, p07, and 800720.00) insertintoORDERSvalues ('1013,013, paranormal, p0720.00) ) insertintoORDERSvalues ('1026 paraphernalia p002) insertintoORDERSvalues (' 1026 paraphernalia p002) p05p05p05p05p05p05p05p05p05mp05p05p05p05p05p05p05p080.00) insertintoORDERSvalues ('1015p015mp03p03p080.00) insertintoORDERSvalues (' 1015p015p003mc003p004.00) insertintoORDERSvalues ('1021p01p06p06mp01p0100.00) insertintoORDERSvalues (' 1016p016p006p06p06mp01p06p01') ) insertintoORDERSvalues ('1020 memory, p07', p07,) insertintoORDERSvalues (' 1024,024,000,500.00) insertintoORDERSvalues (1020,000,500.00)

2. The compilation of sql statement.

Question 1: find out the aid value and name of the agent living in New York

Selectaid,anamefromAGENTSwherecity='NewYork'

-- question 2: retrieve the pid values of all parts in the order record

SelectdistinctpidfromORDERS

-- question 3: retrieve all customer-agent name pairs (cname,aname) that meet the following criteria, in which cname has ordered goods through aname (has made a mistake)

Createtableb (cidvarchar (10), aidvarchar (4)) insertintobselectcid,aidfromORDERSgroupbycid,aid

Selectdistinctaname,cnamefromAGENTS,CUSTOMERS,bwhereAGENTS.aid=b.aidandCUSTOMERS.cid=b.cid

-- the above results are obtained by building a temporary table, and if you write it directly (note that there must be distinct)

Selectdistinctcname,anamefromagents,customers,orderswhereagents.aid=orders.aidandcustomers.cid=orders.cid

-- answer:

SelectdistinctCUSTOMERS.cname,AGENTS.anamefromCUSTOMERS,AGENTS,ORDERSwhereCUSTOMERS.cid=ORDERS.cidandAGENTS.aid=ORDERS.aid

As you can see, the answer is very simple

How to operate SQLServer database creation database and table creation and conditional query

Question 4: generate a "table" with columns ordno,cid,aid,pid and profit on the basis of the orders table, in which the profit is calculated by quantity and price by subtracting 60% of the sales revenue from total sales revenue. Discounts for customers and percentage of fees for agents

Answer: this question tells us the importance of adding the keyword [], otherwise there will always be grammatical errors near the keyword 'percent'. "

Selectordno,x.cid,x.aid,x.pid,40* (x.qty*p.price)-01* (c.discnt+ a.[ steps]) * (x.qty*p.price) asprofitfromordersasx,customersasc,agentsasa,productsaspwherec.cid=x.cidanda.aid=x.aidandp.pid=x.pid

-- question 5: find out the right for customers living in the same city.

Selectdistinctc1.cname,c2.cname,c1.cityfromcustomersc1,customersc2wherec1.city=c2.cityandc1.cname

The key point of this question is that you can give two aliases to a table. This method can be used if the topic requires a match to be found in the same column of a table.

Question 6: find out the pid value of a product ordered by at least two customers

Number of customers ordered by selectpid,count (cid) as fromordersgroupbypidhavingcount (cid) > = 2

It is better to use this method at this time

-- answer:

Selectdistinctx1.pidfromordersx1,ordersx2wherex1.pid=x2.pidandx1.cid

Summary: most operations on a table can be done by aliasing, and there should be redundancy.

Question 6: inquire about the cidvalue of customers who have ordered products ordered by an agent A06

Selectdistinctcidfromorderswherepidin (selectpidfromorderswhereaid='a06')

-- it feels natural to use in at this time, because what we are going to go to is the product ordered by "some" agent A06.

Similarly, this operation in a table can be done by aliasing (and this way is simpler)

Selectdistincty.cidfromordersx,ordersywherey.pid=x.pidandx.aid='a06'

Summary: you can use one query at a time and get an alias through the table.

About the SQL Server database to create the database and create tables and conditions query how to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.