LICT .NET Training | Kamal Protap Sir

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

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

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

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.

1. Connection- This component is used for connecting to the database. The base class is DbConnection
2. Command: This component executes SQL query against the data source. The base class is DbCommand.
3. DataReader-It reads data from data source. It accesses data read only and forward only. The base class is DbDataReader.
4. DataAdapter-It invokes dataset and resolves updates with the data source. The base class is DbDataAdapater

Pass value with parameters:

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 = "";
        }
    }
}