去一美资外包公司,不会做,干脆把题拿回来了Database SkillTable name: tbl_order
Field Name Type Res
Pk_order_id Int Primary Key of tbl_order
Order_date Datetime
Order_customer_name Varchar(255)
Order_status Varchar(1) N = New , D = DoneDataset: tbl_order
Pk_order_id Order_date Order_customer_name Order Status
1 1/1/2007 Customer 1 D
2 1/2/2007 Customer 2 N
3 1/3/2007 Customer 3 N
4 1/3/2007 Customer 4 NTable name: tbl_order_line
Field Name Type Res
Order_line_product_name Varchar(255)
Order_line_unit_price Decimal(10,2)
Order_line_qty Int
Fk_order_id Int Foreign Key of tbl_orderDataset: tbl_order_line
Order_line_product_name Order_line_unit_price Order_line_qty Fk_order_id
Product 1 10.00 10 1
Product 2 20.00 20 1
Product 3 30.00 30 1
Product 4 40.00 40 2
Product 5 50.00 50 3
Product 6 60.00 60 3
Product 7 70.00 70 41. According to above dataset, please make a SQL statement to join tbl_order and tbl_order_line together with getting pk_product_id equal to 12. According to above dataset, please make a SQL statement to select all order with status = ‘N’3. According to above dataset, please make a SQL statement to calculate total sum of each order4. According to above dataset, please make a SQL statement to calculate average amount of each month’s order
Programming Skill (C#)Table name: tbl_member
Field Name Type Res
Pk_memebr_id Int Primary Key of tbl_order, Auto increment
Member_username Varchar(255)
Member_password Varchar(255)
Member_status Varchar(1) N = New , D = Done
File name: Default4.aspx<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"
AutoGenerateSelectButton="True">
</asp:GridView>
</div>
<br />
<asp:FormView ID="FormView1" runat="server"> </asp:FormView>
<br />
</form>
</body>
</html>File Name: Default4.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
Field Name Type Res
Pk_order_id Int Primary Key of tbl_order
Order_date Datetime
Order_customer_name Varchar(255)
Order_status Varchar(1) N = New , D = DoneDataset: tbl_order
Pk_order_id Order_date Order_customer_name Order Status
1 1/1/2007 Customer 1 D
2 1/2/2007 Customer 2 N
3 1/3/2007 Customer 3 N
4 1/3/2007 Customer 4 NTable name: tbl_order_line
Field Name Type Res
Order_line_product_name Varchar(255)
Order_line_unit_price Decimal(10,2)
Order_line_qty Int
Fk_order_id Int Foreign Key of tbl_orderDataset: tbl_order_line
Order_line_product_name Order_line_unit_price Order_line_qty Fk_order_id
Product 1 10.00 10 1
Product 2 20.00 20 1
Product 3 30.00 30 1
Product 4 40.00 40 2
Product 5 50.00 50 3
Product 6 60.00 60 3
Product 7 70.00 70 41. According to above dataset, please make a SQL statement to join tbl_order and tbl_order_line together with getting pk_product_id equal to 12. According to above dataset, please make a SQL statement to select all order with status = ‘N’3. According to above dataset, please make a SQL statement to calculate total sum of each order4. According to above dataset, please make a SQL statement to calculate average amount of each month’s order
Programming Skill (C#)Table name: tbl_member
Field Name Type Res
Pk_memebr_id Int Primary Key of tbl_order, Auto increment
Member_username Varchar(255)
Member_password Varchar(255)
Member_status Varchar(1) N = New , D = Done
File name: Default4.aspx<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"
AutoGenerateSelectButton="True">
</asp:GridView>
</div>
<br />
<asp:FormView ID="FormView1" runat="server"> </asp:FormView>
<br />
</form>
</body>
</html>File Name: Default4.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("...");
con.Open();
System.Data.OleDb.OleDbCommand dataCommand = new System.Data.OleDb.OleDbCommand("select * from tbl_order join tbl_order_line on tbl_order.Pk_order_id=tbl_order_line.Fk_order_id where status='N' order by Order_line_unit_price", con);
System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter();
dataAdapter.SelectCommand = dataCommand;
DataSet dataSet=new DataSet();
dataAdapter.Fill(dataSet, tabelName);
GridView1.DataSource = dataSet.Tables[0].DefaultView;
on pk_order_id=fk_order_id
where pk_order_id=12. select * from tbl_order where status='N'3. select pk_order_id, sum(Order_line_unit_price),sum(order_line_qty) from tbl_order inner join
tbl_order_line on pk_order_id=fk_order_id group by pk_order_id4.不清楚是要每月订单平均数还是要订单金额平均数,这里挑个简单的订单平均数:
select year(order_date) as [YEAR],month(order_date) as [month], avg(count(pk_order_id)) form tbl_order group by year(order_date),month(order_date)编程的不知道要干什么,瞎写几句
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=XXX;...";
SqlDataSource sds = new SqlDataSource(connStr, "SELECT * FROM tbl_member");
GridView1.DataSource = sds;
GridView1.DataKeyNames =new string[] {"Pk_memebr_id"};
GridView1.DataBind();
}
} 不知道如果我面试可以得几分,呵呵呵.
5. Please add C# code into the above code, so that the program can add / edit / delete the member from the database.
5. Please add C# code into the above code, so that the program can add / edit / delete the member from the database.