DDL – Data definition Language(Which deals with database schemas and descriptions that how the data should reside in database) – create,alter,drop, truncate, comment
DML – Data manipulation language(Modify, Update, Delete data in database) – select, insert, update, delete
DCL – Data Control Language(Regarding the rights of the database) grant,revoke
31-10-17 vids
SQL Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Use PracticeByMe select * from dbo.tbl_empdtls --group by select Department, Count(*) as 'Number of Employees' from tbl_empdtls group by Department select Gender, count(*) as Gender from tbl_empdtls group by Gender select Department,Gender, Count(*) as 'Number of Employees' from tbl_empdtls group by Department, Gender order by 2 ASC --'1' it means the first column in the select statement Department --important query --How many Employees who is hired in each year select YEAR(HireDate) as HireYear,count(*) as 'Number of Employee' from tbl_empdtls group by year(HireDate) order by count(*) desc --select min sal,max sal, avg sal in each department select JobTitle, min(Salary) as 'MinSalary', max(Salary) as 'Maximum', avg(Salary) as 'AverageSalary' from tbl_empdtls group by JobTitle order by MinSalary |
SQL Aggregate functions, slides presentation: This presentation describes SQL Aggregate function covering SQL COUNT(), SUM(), MAX(), MIN(), AVG) functions with examples.
SubQuery:
A query within another query is called subquery or nested query . Inner Query, Outer query is in subquery.
Sub Query are two types they are, co-related sub query and non co-related subquery. They works for both DDL and DML statements
Co-related means relationship/dependent on
Non co-related means independent
There are two queries in sub query. You will see group by, order by is single type query but in sub query it needs multiple query
Non co related: They are two types,
Inner query: It will first execute. It will return some result. Independently it will execute.
Outer Query: Based on the inner query result now outer query will execute. The whole process is known as non co-related
|
|
--who is getting the minimum salary in the table select * from tbl_empdtls where salary = (select min(salary) from tbl_empdtls) |
|
|
--select subquery select * from customers where ID IN(select ID FROM CUSTOMERS WHERE salary>25000) |
= for comapring single value
IN for comparing multiple value
|
|
--insert subquery create table customers_backup(ID int primary key,name varchar(20),age int,salary numeric(9,2), address varchar(20)); insert into Customers_backup select * from customers where ID in(select ID from CUSTOMERS); select * from customers_backup |
|
|
--update sub query update customers SET SALARY=SALARY*0.25 WHERE AGE IN(select age from customers_backup where age>23) |
|
|
--delete subquery delete from customers where AGE IN(select AGE from customers_backup where age>=25) |
|
|
--select the details of the product not yet sale at least once --non co related select id ,name,[Description] from tbl_products where id not in(select distinct ProductId from tbl_ProductSales) --subquery and join is same select p.Id,p.Name,p.[Description] from tbl_products p left join tbl_ProductSales ps on p.Id=ps.ProductId where ps.ProductId is null |
Co-related: It is dependent query. Inner query can’t execute independently. Inner query depends on outer query. Without returning row from the outer query inner can’t execute. In co-related sub query. All execute first.
|
|
--co related select name, (select sum(quantity) from tbl_ProductSales where ProductId=tbl_Products.Id) as QuantitySold from tbl_products |
02.11.17 Vids
Store Procedure:
MS SQL Server store procedure is used to save time write the same code again and again for storing in database and also get the rquired output by passing parameters.
Syntax:
Following the basic syntax of stored procedure creation.
|
|
create procedure <procedure name> AS BEGIN <SQL STATEMENT> END Go |
Lots of parameters used in stored procedure:
IN paramater is a paramater whose values are passed inside of the procedure
OUT paramater is a paramater whose value is pass outside the parameter
OUT parameter return some value
IN does not return any value
create procedure
|
|
create proc GetStudentNamesInOutVariable ( @studentid INT, @studentname varchar(200) OUT, @studentEmail varchar(200) OUT ) AS SELECT @studentname=FName+' '+LName, @studentEmail=Email FROM Students_INFO WHERE StudID=@studentid |
Executing the stored procedure:
|
|
--Execute the procedure DECLARE @studentname VARCHAR(20) DECLARE @studEmail VARCHAR(20) EXEC GetStudentNamesInOutVariable 1, @studentname OUTPUT, @studEmail OUTPUT SELECT @studentname as StudentName, @studEmail as StudentEmail |
Here are the store procedure example basic in my blog
http://zakilive.com/2017/12/14/stored-procedures/
Creating View:
View is something where other developers can’t find details. But they can make query with the name when they need it.
View helps to show the particular data for the
|
|
create view VW_Select as select Department,Gender from tbl_empdtls select * from tbl_empdtls |
UPDATE with view
|
|
update VW_Select set Gender='female' where Department='cse' select * from VW_Select |
|
|
--condition in view alter view view_department AS SELECT Department from tbl_empdtls where Department='cse' select * from view_department |
View don’t support order by in that case we need top keyword in view:
|
|
create view VW_OrderbyFname as select top 2* from tbl_empdtls order by Fname select * from dbo.tbl_empdtls |
05.11.2017 vids
ADO.NET is a set of classes that allows you to connect and work with data sources like databases,excel file, access file, xml file,my sql, and notepad.
To connect your application with different data sources of database you need to know the right data provider. There are sevral data providers in ADO.NET that connects with different types of sources.
There are 4 core components of .NET data providers that is used to connect,access and retrieve data from the database.
- Connection- This component is used for connecting to the database. The base class is DbConnection
- Command: This component executes SQL query against the data source. The base class is DbCommand.
- DataReader-It reads data from data source. It accesses data read only and forward only. The base class is DbDataReader.
- DataAdapter-It invokes dataset and resolves updates with the data source. The base class is DbDataAdapater
Pass value with parameters:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
using System; using System.Data.SqlClient; using System.Windows.Forms; namespace WindowsFormsApp1 { public partial class datesetProgs : Form { public datesetProgs() { InitializeComponent(); } private void datesetProgs_Load(object sender, EventArgs e) { } private void textBox1_TextChanged(object sender, EventArgs e) { } private void label1_Click(object sender, EventArgs e) { } private void dateTimePicker1_ValueChanged(object sender, EventArgs e) { textDate.Text = dateTimePicker1.Text; dateTimePicker1.Visible = false; } private void btnSubmit_Click(object sender, EventArgs e) { string ConString = "Server=ZAKIHP-PC\\ZAKISQL;Database=PracticeByMe;Trusted_Connection=Yes"; SqlConnection conn = new SqlConnection(ConString); //string query = "INSERT into tbl_ProductInfo(Name,Price,Dates) values('"+textBox1.Text+"','"+textBox2.Text+"','"+textDate.Text+"')"; string query = "INSERT into tbl_ProductInfo(Name,Price,Dates) values(@Names,@Price,@Dates)"; SqlCommand cmd = new SqlCommand(query, conn); //pass value to parameters cmd.Parameters.AddWithValue("@Name", textBox1.Text); cmd.Parameters.AddWithValue("@Price", textBox2.Text); cmd.Parameters.AddWithValue("@Dates", textDate.Text); try { conn.Open(); int result = cmd.ExecuteNonQuery(); MessageBox.Show(result + " Records inserted successfully!"); Clear(); } catch (Exception ex) { MessageBox.Show("Error generated details:" + ex.ToString()); } finally { conn.Close(); } dateTimePicker1.Visible = true; } public void Clear() { textBox1.Text = ""; textBox2.Text = ""; textDate.Text = ""; } } } |
