COMMANDTEXT
TABLE_1
TABLE_3
SELECT name,english+science+social as sum FROM TABLE_1
SELECT * FROM TABLE_1 WHERE LEN(NAME) =3
SELECT * FROM TABLE_1 WHERE NAME = 'S'
SELECT * FROM TABLE_1 WHERE NAME LIKE '%W%'
SELECT * FROM TABLE_1 WHERE SCIENCE LIKE '[5-9][0-9]'
SELECT * FROM TABLE_1 WHERE GENDER LIKE '[^F]%'
SELECT * FROM TABLE_1 WHERE ENGLISH LIKE '[5-9][0-9]' AND SCIENCE LIKE'[5-9][0-9]'AND SOCIAL LIKE'[5-9][0-9]'
SELECT TOP 5 * FROM TABLE_1 ORDER BY NAME ASC
SELECT TOP 5 *,ENGLISH + SCIENCE+SOCIAL AS SUM FROM TABLE_1 ORDER BY ENGLISH + SCIENCE+SOCIAL ASC
SELECT TOP 4 *, ENGLISH+SOCIAL+SCIENCE AS SUM FROM (SELECT TOP 7 * FROM TABLE_1 ORDER BY ENGLISH+SCIENCE+SOCIAL DESC) AS MIDDLE ORDER BY ENGLISH+SCIENCE+SOCIAL ASC
//select student whose total ranks 4-7
SELECT TOP 20 PERCENT * FROM TABLE_1 ORDER BY SOCIAL DESC
SELECT * FROM TABLE_1 WHERE ENGLISH > 40 + FLOOR(RAND()*20)
SELECT CLASS,AVG(ENGLISH) AS AVGENG, AVG(SCIENCE) AS AVGSIC, AVG(SOCIAL) AS AVGSOCI FROM TABLE_1 GROUP BY CLASS
SELECT CLASS,GENDER, AVG(ENGLISH) AS AVGENG, AVG(SCIENCE) AS AVGSIC, AVG(SOCIAL) AS AVGSOCI FROM TABLE_1 GROUP BY GENDER, CLASS
SELECT CLASS,GENDER, AVG(ENGLISH) AS AVGENG, AVG(SCIENCE) AS AVGSIC, AVG(SOCIAL) AS AVGSOCI FROM TABLE_1 WHERE ENGLISH > 40 AND SOCIAL > 40 AND SCIENCE >40 GROUP BY ALL GENDER, CLASS
SELECT CLASS,GENDER, AVG(ENGLISH) AS AVGENG, AVG(SCIENCE) AS AVGSIC, AVG(SOCIAL) AS AVGSOCI FROM TABLE_1 GROUP BY GENDER, CLASS WITH CUBE
SELECT CLASS,GENDER, AVG(ENGLISH) AS AVGENG, AVG(SCIENCE) AS AVGSIC, AVG(SOCIAL) AS AVGSOCI FROM TABLE_1 GROUP BY GENDER, CLASS WITH ROLLUP ORDER BY AVG(ENGLISH) DESC
SELECT X.GENDER, AVG(X.ENGLISH) AS AVG1, Y.GENDER, AVG(Y.ENGLISH) AS AVG2 FROM TABLE_1 AS X, TABLE_2 AS Y GROUP BY X.GENDER, Y.GENDER
SELECT * FROM TABLE_1 WHERE SOCIAL IN (SELECT MAX(SOCIAL) FROM TABLE_1)
SELECT CLASS, COUNT(CLASS) AS NUM FROM TABLE_1 GROUP BY CLASS
SELECT NAME,GENDER,SOCIAL FROM TABLE_1 WHERE SOCIAL > (SELECT AVG(SOCIAL) FROM TABLE_1)
SELECT NAME, ENGLISH, SCIENCE FROM TABLE_1 UNION SELECT NAME, ENGLISH, SCIENCE FROM TABLE_3
SELECT NAME, ENGLISH FROM TABLE_1 WHERE NAME IN (SELECT NAME FROM TABLE_3 WHERE GF>0)
SELECT * FROM TABLE_1 WHERE GENDER = 'F' AND SOCIAL > ALL (SELECT SOCIAL FROM TABLE_1 WHERE GENDER = 'M')
SELECT X.NAME, X.SOCIAL, Y.GF FROM TABLE_1 AS X INNER JOIN TABLE_3 AS Y ON X.NAME = Y.NAME WHERE GENDER = 'M' AND Y.GF > ANY(SELECT Y.BF FROM TABLE_3 AS Y INNER JOIN TABLE_1 AS X ON X.NAME = Y.NAME WHERE X.GENDER='F')
//select M whose GF > minimum BF other Fs have.
SELECT X.NAME, X.GENDER, Y.GF, Y.BF FROM TABLE_1 AS X INNER JOIN TABLE_3 AS Y ON X.NAME = Y.NAME WHERE (GENDER = 'M' AND Y.GF > Y.BF) OR (GENDER = 'F' AND Y.BF > Y.GF) ORDER BY X.GENDER DESC
//select M who has more GF than BF, and F who has more BF than GF
SELECT NAME, SCIENCE, (SELECT AVG(SCIENCE) FROM TABLE_1) AS AVGSCI FROM TABLE_1 GROUP BY NAME,SCIENCE HAVING SCIENCE > (SELECT AVG(SCIENCE) FROM TABLE_1)
UPDATE Table_1 SET ENGLISH = ((SELECT MAX(ENGLISH) FROM TABLE_1)+1) WHERE NAME = 'S'
//DELETE FROM TABLE_2 WHERE NAME IN (SELECT NAME FROM TABLE_2 WHERE GENDER = 'F' AND CLASS = 'D')
SELECT NAME,GENDER FROM TABLE_2 INTERSECT SELECT NAME,GENDER FROM TABLE_1
SELECT NAME,GENDER FROM TABLE_2 EXCEPT SELECT NAME,GENDER FROM TABLE_1
SELECT TABLE_1.NAME,TABLE_1.GENDER, TABLE_3.BF,TABLE_3.GF, TABLE_2.CLASS FROM TABLE_1 INNER JOIN TABLE_3 ON TABLE_1.NAME=TABLE_3.NAME INNER JOIN TABLE_2 ON TABLE_1.NAME =TABLE_2.NAME
SELECT TABLE_1.NAME,TABLE_1.GENDER, TABLE_3.BF,TABLE_3.GF FROM TABLE_1 INNER JOIN TABLE_3 ON TABLE_1.NAME=TABLE_3.NAME AND TABLE_3.GF>TABLE_3.BF AND TABLE_1.GENDER = 'M'
SELECT * FROM TABLE_2 WHERE NAME NOT IN (SELECT NAME FROM TABLE_1)
SELECT TABLE_1.NAME, TABLE_1.GENDER, (CASE TABLE_1.GENDER WHEN 'M' THEN TABLE_3.GF ELSE TABLE_3.BF END) AS HETEROSEX FROM TABLE_1 INNER JOIN TABLE_3 ON TABLE_1.NAME = TABLE_3.NAME
//select opposite sex
CREATE UNIQUE INDEX ID ON TABLE_3(NAME)
//insert same names
//DROP INDEX TABLE_2.ID
UPDATE Table_3 SET BIRTHDAY = DATEADD(DAY,2,(SELECT BIRTHDAY FROM TABLE_3 WHERE NAME = 'S')) WHERE NAME ='S'
//reference: http://www.w3schools.com/sql/func_dateadd.asp
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace sql
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
button1.Focus();
/* delete("Table_1");
delete("Table_3");
delete("Table_2");
Random r = new Random();
for (int i = 0; i < 10; i++)
{
string name = "";
for (int j = 0; j < r.Next(1,8); j++)
{
name += (char)('A' + r.Next(26));
}
char gender = 'M';
if (r.Next(2) == 1)
{
gender = 'F';
}
char class1 = 'A';
switch (r.Next(3))
{
case 0:
class1 = 'A';
break;
case 1:
class1 = 'B';
break;
case 2:
class1 = 'C';
break;
default:
break;
}
textBox1.Text = "INSERT INTO Table_1(name,gender,english,science,social,class)VALUES('" + name + "' ,'" + gender + "'," + r.Next(100) + "," + r.Next(100) + "," + r.Next(100) + ",'" + class1 + "')";
insertrow();
textBox1.Text = "INSERT INTO Table_2(name,gender,english,science,social,class)VALUES('" + name + "' ,'" + gender + "'," + r.Next(100) + "," + r.Next(100) + "," + r.Next(100) + ",'" + class1 + "')";
insertrow();
textBox1.Text = "INSERT INTO Table_3(NAME,BF,GF)VALUES('" + name + "'," + r.Next(6) + "," + r.Next(6) + ")";
insertrow();
}*/
// for(int j=0; j<10;j++)
// {
textBox2.Text = "UPDATE Table_3 SET BIRTHDAY = '1990/01/01' WHERE NAME ='S'";
update();
}
public void insertrow()
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=study;Integrated Security=SSPI;");
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = textBox1.Text;
command.ExecuteNonQuery();
connection.Close();
}
public void insertcolume()
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=study;Integrated Security=SSPI;");
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "alter table [Table_1] add [ProductId] int default 0 NOT NULL";
command.ExecuteNonQuery();
connection.Close();
}
public DataTable display()
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=study;Integrated Security=SSPI;");
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = textBox4.Text;
adapter.SelectCommand = command;
DataTable table = new DataTable("test");
adapter.Fill(table);
connection.Close();
return table;
}
public void delete()
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=study;Integrated Security=SSPI;");
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = textBox3.Text;
command.ExecuteNonQuery();
connection.Close();
}
public void update()
{
SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=study;Integrated Security=SSPI;");
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = textBox2.Text;
command.ExecuteNonQuery();
connection.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = display();
}
private void button2_Click(object sender, EventArgs e)
{
insertrow();
}
private void button3_Click(object sender, EventArgs e)
{
delete();
}
private void textBox4_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.Enter)
{
dataGridView1.DataSource = display();
}
}
private void button4_Click(object sender, EventArgs e)
{
update();
}
private void textBox2_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.Enter)
{
update();
}
}
}
}
No comments:
Post a Comment