Showing posts with label sql server. Show all posts
Restaurant Management System project with ADO.NET and Database
Restaurant Management System is a windows application with two modules - Consumer and Admin.
SQL Server database is used to store data
Click here to download
SQL Server database is used to store data
Click here to download
Tag :
sql server
,
Windows Applications
Examples of SQL Commands - DDL, DML
Example for SQL commands – DDL,
DML
--Create database Db_Product and check it in Databases Folder
create database
Db_Product
--using product database (Selected in databases dropdown)
use Db_Product
--create table Tbl_Items with 3 cols - Id int, ItemName
vc(30), ItemPrice money (Refresh tables folder)
create table
Tbl_Items(Id int,ItemName varchar(30),ItemPrice money)
--view data present in table
select * from Tbl_Items
--insert rows of data for Tbl_Items
insert into
Tbl_Items values(1,'Biryani',150.00)
insert into
Tbl_Items(Id,ItemName) values(1,'Noodles')
insert into
Tbl_Items(Id,ItemName,ItemPrice) values(1,'Coke',20)
--view data present in table
select * from Tbl_Items
--view data of two cols in table
select Id,
ItemName from Tbl_Items
--adding a new column Availablity (varchar(10))to the table
Alter table
Tbl_Items
Add Availability varchar(10)
--view data present in table
select * from Tbl_Items
--modify the datatype of availablity column to char
Alter table
Tbl_Items
Alter column
Availability char
--update Id as 2 for an item where ItemName is Noodles
update Tbl_Items set
Id=2 where
ItemName='Noodles'
--update Id as 3 for an item where ItemName is Coke
update Tbl_Items set
Id=3 where
ItemName='Coke'
--update Availability of all the items to A
update Tbl_Items set
Availability='A'
--view data present in table
select * from Tbl_Items
--delete an item data where id is 2
delete from
Tbl_Items where Id=2
--view data present in table
select * from Tbl_Items
--delete column ItemPrice
alter table
Tbl_Items
drop column
ItemPrice
--view data present in table
select * from Tbl_Items
--change column name to Status
sp_rename 'Tbl_Items.Availability','Status'
--view data present in table
select * from Tbl_Items
--changing Tablename to Tbl_ProductItems
sp_rename 'Tbl_Items','Tbl_ProductItems'
--to view data after changing table name
select * from Tbl_ProductItems
--clear the entire table data
truncate table
Tbl_ProductItems
--to view data present in table
select * from Tbl_ProductItems
--delete table
drop table
Tbl_ProductItems
--refresh the tables folder and you cant see the table there
--get data from deleted table
select * from Tbl_ProductItems --Invalid
object name
--delete database(can't delete if you are using it)
drop database
Db_Product
--refresh the database folder and see the database will be
missing
--You can't use this database any more
Tag :
sql server
Tag :
sql server
TCL commands
B – Begin Transaction (To start recording transactions)
C – Commit (To save the transaction permanently)
R – Rollback (To revert the database to the state where recording started)
C – Commit (To save the transaction permanently)
R – Rollback (To revert the database to the state where recording started)
Tag :
sql server
DML commands
SUDI
S – Select (To retrieve data)U – Update (to update data)
D – Delete(to delete rows of data)
I – Insert (to insert new row of data)
Tag :
sql server
DDL commands
DRCAT
D – Drop (To delete a database or table)
R – Rename (To change the column name)
C – Create (To create a database or table)
A – Alter (To work on columns)
T – Truncate(To clear entire table data)
Alter contains three commands:
Alter Add (To add column)
Alter alter (To modify data type of column)
Alter drop (To delete a column)
Tag :
sql server
Introduction to SQL
SQL is a language used to interact with the database.
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard.
SQL is not case sensitive.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.
In RDMS data is stored in the form of tales which contains rows and columns.
SQL is divided into 4 Sub languages as follows:
DDL (Data Definition Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)
DCL (Data Control Language)
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard.
SQL is not case sensitive.
RDBMS
RDBMS stands for Relational Database Management System.RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.
In RDMS data is stored in the form of tales which contains rows and columns.
SQL is divided into 4 Sub languages as follows:
DDL (Data Definition Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)
DCL (Data Control Language)
Tag :
sql server
SQL Server 2012 Select query
Select
select 'test'When you execute the above select query it will print test as output
select * from tbl_register
The above query is used to print the whole table data named as tbl_register.
select name from tbl_register
The above query is used to print data of name column from table named as tbl_register.
Where
select name from tbl_register where id<3The above query is used to print data of name column from tbl_register table with id value less than 3 only.
select id, name from tbl_register where id<3 order by name
The above query is used to print data from id and name columns and the output will be sorted based on the name column data.
Distinct
select distinct name from tbl_registerThe above query will print data in name column without duplicates or repeated values.
alias
select name as username from tbl_register
select username = name from tbl_register
select name username from tbl_register
The above three queries will print data in name column with username as column name.
select name from tbl_register as register
select name from tbl_register register
The above two queries will create register as alias name for table name tbl_register which is used in joins.
case
select id, name,
case stat
when 'yes' then 'pass'
else 'fail'
end as
[status]
from tbl_register
The above query will print three columns id, name, status.
id, name columns data is printed along with one more column named as status which is not present in table.
the data under status column is based on stat column which is present under tbl_register.
if stat column value is yes then at that place pass is printed else fail is printed in status column in the output.
id, name columns data is printed along with one more column named as status which is not present in table.
the data under status column is based on stat column which is present under tbl_register.
if stat column value is yes then at that place pass is printed else fail is printed in status column in the output.
id name status
1 sudhir pass
2 raj fail
3 ram pass
4 gopi pass
5 sudhir fail
Joins
Inner joins
Outer joins
Cross joins
Tag :
sql server
SQL Server 2012
Operators
Predicates - in, between, like
Comparision operators - <, >, =, <=, >=, <>, !=, !<, !>
Logical operators - and, or, not
Arthimetic operators - +, -, * , /, %
Concatenation - +
Functions
String function - substring, left, right, len, datalength, replace, replicate, upper, lower, utrim, ltrim
date and time functions - getdate, systdatetime, getutcdate, dateadd, datediff, year, month, day
aggregate functions - sum, min, max, avg, count
T-SQL
Controlling flow - if else, while, break, continue, begin end
error handling - try catch
transaction control - begin transaction, commit transaction, rollback transaction
Comments - -- or /* */
Predicates - in, between, like
Comparision operators - <, >, =, <=, >=, <>, !=, !<, !>
Logical operators - and, or, not
Arthimetic operators - +, -, * , /, %
Concatenation - +
Functions
String function - substring, left, right, len, datalength, replace, replicate, upper, lower, utrim, ltrim
date and time functions - getdate, systdatetime, getutcdate, dateadd, datediff, year, month, day
aggregate functions - sum, min, max, avg, count
T-SQL
Controlling flow - if else, while, break, continue, begin end
error handling - try catch
transaction control - begin transaction, commit transaction, rollback transaction
Comments - -- or /* */
Tag :
sql server
sql server stored procedure variables parameters and if condition
sql queries in ms sql server for creating a table used to store registration data using stored procedure which takes inputs using input parameters.
Before inserting the email id given as input is checked wheather it is already existing in the table or not.
To check select command which returns the count of rows existing with that emailid.
The count returned by select command is stored in a variable named as u.
Variable u is created using a keywork declare.
In the below example i also used if condition to check the value present in variable u.
sql queries in ms sql server
create database db_connect
use db_connect
create table tbl_Register(sno bigint identity primary key,emailid varchar(50) unique,pwd varchar(15),gender varchar(10))
insert into tbl_Register(emailid,pwd,gender) values('sudhir@gmail.com','sudhir','Male')
select * from tbl_Register
alter procedure sp_register
@emailid varchar(50),
@pwd varchar(15),
@gender varchar(10)
as begin
--check emailid is unique
declare @u int=0
select @u=(select count(emailid) from tbl_Register where emailid=@emailid )
--register with unique emailid only
if(@u=0)
begin
insert into tbl_Register values(@emailid,@pwd,@gender )
end
end
exec sp_register 'govind@gmail.com','rani','Female'
Before inserting the email id given as input is checked wheather it is already existing in the table or not.
To check select command which returns the count of rows existing with that emailid.
The count returned by select command is stored in a variable named as u.
Variable u is created using a keywork declare.
In the below example i also used if condition to check the value present in variable u.
sql queries in ms sql server
create database db_connect
use db_connect
create table tbl_Register(sno bigint identity primary key,emailid varchar(50) unique,pwd varchar(15),gender varchar(10))
insert into tbl_Register(emailid,pwd,gender) values('sudhir@gmail.com','sudhir','Male')
select * from tbl_Register
alter procedure sp_register
@emailid varchar(50),
@pwd varchar(15),
@gender varchar(10)
as begin
--check emailid is unique
declare @u int=0
select @u=(select count(emailid) from tbl_Register where emailid=@emailid )
--register with unique emailid only
if(@u=0)
begin
insert into tbl_Register values(@emailid,@pwd,@gender )
end
end
exec sp_register 'govind@gmail.com','rani','Female'
 |
| MS Dotnet ninja |
Tag :
sql server