Tuesday 4 March 2014

C# sql



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'





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