TRY_SQL, Sql Server

TRY_SQL

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'
--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

/* Create a table called NAMES */
CREATE TABLE NAMES(Id int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Age int);

/* Create few records in this table */
INSERT INTO NAMES VALUES(1,'Tom','Ashli',20);
INSERT INTO NAMES VALUES(2,'Lucy','Catton',22);
INSERT INTO NAMES VALUES(3,'Frank','Ace',33);
INSERT INTO NAMES VALUES(4,'Jane','Brontee',44);
INSERT INTO NAMES VALUES(5,'Robert','Cruise',58);

/* Display all the records from the table */

/*SELECT FirstName, Age FROM NAMES
where LastName='Ace';*/

update NAMES
set Age=38
where LastName='Ace';

ALTER TABLE NAMES 
ADD Gender varchar(50)

update NAMES
set Gender='M'

update NAMES
Set Gender ='F'
where Id=2

update NAMES
Set Gender ='F'
where FirstName='Jane'
and LastName like 'Bronte_'

--delete NAMES
--where FirstName='Robert'

create table products
(id int primary key identity(1,1),ProductName varchar(50),Price float);
    insert into Products Values ('Baseball', 5.7);
    insert into Products values ('Bat',99.9);
    insert into Products values ('Football cap', 14.3);
    insert into Products values ('socks', 10.8)
    
    
    
    
 create table Orders
 (
   OrderID int primary key identity(1,1),
     OrderDate Datetime,
     customerID int,
     ProductID int,
     )
   
    
    insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),2,4);
     insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),1,4);
      insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),5,3);
       insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),4,3);
        insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),3,2);
         insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),2,2);
          insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),3,1);
           insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),5,1);
            insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),1,1);
             insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),1,2);
              insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),1,3);
               insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),3,4);
                insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),3,4);
                 insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),4,3);
                  insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),2,2);
                   insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),6,5);
                    insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),4,2);
                     insert into Orders (OrderDate, customerID, ProductID) values (GetDate(),1,3);
    
  -- select * from products
  --  select * from NAMES
      select NAMES.LastName, sum(products.Price), Products.ProductName  as Total
      from Orders
     
      
inner join NAMES on Orders.customerID=NAMES.id
inner join Products on Orders.ProductID=Products.id

group by NAMES.LastName, Products.ProductName


run \| edit \| history \| help	0