Task - Stored Procedures - classicmodels DB #10

akash-coded · 2022-03-15T07:35:28Z

akash-coded
Mar 15, 2022
Maintainer

Database = `classicmodels`

Dump file: classicmodels.sql

DB Schema

Stored Procedures

Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.
Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

aish955 · 2022-03-15T07:40:43Z

aish955
Mar 15, 2022

delimiter //
mysql> create procedure getemployees()
    -> begin
    -> select concat(firstname,' ',lastname),city,state,country from employees inner join offices using (officecode);
    -> end
    -> //
delimiter ;
mysql> call getemployees();


delimiter //
mysql> create procedure getpayments()
    -> begin
    -> select customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber);
    -> end
    -> //
 delimiter ;
mysql> call getpayments();

0 replies

ratnadeepikavuddagiri · 2022-03-15T07:40:49Z

ratnadeepikavuddagiri
Mar 15, 2022

delimiter //
create procedure getAllEmployees()
begin
select concat(firstName," ",lastName) as fullName,city,state,country from employees,offices where employees.officeCode = offices.officeCode;
end//
delimiter ;

delimiter //
create procedure getPayments()
begin
select customerName,checkNumber,paymentDate,amount from customers,payments where customers.customerNumber=payments.customerNumber;
end//
delimiter ;

0 replies

shreiyarandive · 2022-03-15T07:44:33Z

shreiyarandive
Mar 15, 2022

delimiter //
create procedure getAllEmployees() begin select concat(e.firstName, " ", e.lastName), o.city, o.state, o.country from employees e, offices o where e.officeCode = o.officeCode; end //
delimiter ;

call getAllEmployees();




delimiter //
create procedure getpayments()  begin select customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber); end //

 delimiter ;
call getpayments();

0 replies

RohitNaikade1 · 2022-03-15T07:45:06Z

RohitNaikade1
Mar 15, 2022

DELIMITER //
CREATE PROCEDURE get_employees()
BEGIN
	select concat(firstname,' ',lastname),city,state,country from employees inner join offices using (officecode);
END
//
DELIMITER ;

call get_employees();


DELIMITER //
CREATE PROCEDURE getpayments()
BEGIN
	select customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber);
END
//
DELIMITER ;

call getpayments();

0 replies

Sarfinaa · 2022-03-15T07:46:41Z

Sarfinaa
Mar 15, 2022

delimiter // 
create procedure getAllEmployees()
 begin
select
  concat(e.firstname, ' ', e.lastname),
  o.city,
  o.state,
  o.country
from
  employees e
  inner join offices o using(officecode);
end // 
delimiter;
call getAllEmployees();
delimiter // 
create procedure getpayments() begin
select
  customername,
  checknumber,
  paymentdate,
  amount
from
  customers
  inner join payments using (customernumber);
end // 
delimiter;
call getpayments()

0 replies

RohitN09 · 2022-03-15T07:47:21Z

RohitN09
Mar 15, 2022

DELIMITER $$
CREATE PROCEDURE getAllEmployees()
BEGIN
    select concat(firstName," ",lastName) as fullName, o.city, o.state, o.country from employees e inner join offices o using (officeCode);
END $$
DELIMITER ;


DELIMITER &&
CREATE PROCEDURE getPayments()
BEGIN
    select customerName, p.checkNumber, p.paymentDate, p.amount from customers inner join payments p using (customerNumber);
END &&
DELIMITER ;

CALL getPayments();

CALL getAllEmployees();

0 replies

Snehil6197 · 2022-03-15T07:48:12Z

Snehil6197
Mar 15, 2022

DELIMITER //
CREATE PROCEDURE getEmployees()
BEGIN
SELECT CONCAT(firstName,' ',lastName) AS NAME, of.city,of,state,of.country
FROM employees em
INNER JOIN offices of
ON em.officeCode=of.officeCode;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE getPayments()
BEGIN
SELECT customerName ,p.checkNumber,p.paymentDate,p.amount FROM customers c
INNER payments p of
ON c.customerNumber=p.customerNumber
END //
DELIMITER ;

0 replies

preeti-max · 2022-03-15T07:48:36Z

preeti-max
Mar 15, 2022

DELIMITER //
CREATE PROCEDURE get_employees()
BEGIN
select concat(firstname,' ',lastname),city,state,country from employees inner join offices using (officecode);
END
//
DELIMITER ;

call get_employees();

DELIMITER //
CREATE PROCEDURE getpayments()
BEGIN
select customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber);
END
//
DELIMITER ;

call getpayments();``

0 replies

ujjwal18bit1167 · 2022-03-15T07:50:22Z

ujjwal18bit1167
Mar 15, 2022

DELIMITER $$
CREATE Procedure getAllEmployees()
BEGIN
    select CONCAT(firstName," ", lastName) ,city, state, country from 
    employees inner join offices on employees.officeCode = offices.officeCode;
END $$
DELIMITER ;
show PROCEDURE status like 'getAllEmployees';

CALL getAllEmployees();


DELIMITER //

CREATE PROCEDURE getPayments()
BEGIN
  select customerName,checkNumber, paymentDate, amount from  customers inner join payments using (customernumber);
END //
DELIMITER ;  
Call getPayments();

0 replies

Shubhi27 · 2022-03-15T07:50:26Z

Shubhi27
Mar 15, 2022

DELIMITER $$
CREATE PROCEDURE getAllEmployes()
BEGIN
 SELECT concat(firstname,' ',lastname) as fullName,city,state,country from employees inner join offices using (officecode);
END $$

DELIMITER ;

CALL getAllEmployes();



DELIMITER $$
CREATE PROCEDURE getpayments()
     BEGIN
    SELECT customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber);
    END $$
 DELIMITER ;
Call getpayments();

0 replies

KaminiJha · 2022-03-15T07:51:37Z

KaminiJha
Mar 15, 2022

DELIMITER @@
CREATE Procedure getModAllEmployee()
BEGIN
SELECT concat(firstName," ",lastName) as fullName, city,state,country from employees INNER JOIN offices USING(officeCode) LIMIT 10; 
END @@
DELIMITER ;
CALL getModAllEmployee()


DELIMITER @@
CREATE Procedure getPayments()
BEGIN
Select customerName,checkNumber,paymentDate,amount from customers INNER join payments using(customerNumber);
END @@
DELIMITER ;
CALL getPayments()

0 replies

Batchusatyasri · 2022-03-15T07:53:03Z

Batchusatyasri
Mar 15, 2022

```EMPLOYEES PROCEDURE

DELIMITER $$
mysql> CREATE PROCEDURE get_emp_data()
   -> BEGIN
   -> SELECT firstName,lastName,c.city,c.state,c.country from employees inner join offices c using(officeCode);
   -> END $$
Query OK, 0 rows affected (0.04 sec)

mysql> CALL get_emp_data();
   -> DELIMITER $$

**PAYMENT PROCEDURE**

DELIMITER &&
mysql> CREATE PROCEDURE get_payment()
   -> BEGIN
   -> SELECT customerName,checkNumber,paymentDate,amount from customers,payments where customers.customerNumber=payments.customerNumber;
   -> END &&
Query OK, 0 rows affected (0.03 sec)

mysql> CALL get_payment()&&

0 replies

SrideviYamala · 2022-03-15T07:54:00Z

SrideviYamala
Mar 15, 2022

1. DELIMITER $$
CREATE PROCEDURE getAllEmployees()
BEGIN
SELECT concat(firstName," ",lastName) as fullname,city,state,country FROM employees
inner join offices using(officeCode);
END $$
DELIMITER ;
CALL getAllEmployees();

2. DELIMITER $$
CREATE PROCEDURE getPayments()
BEGIN
SELECT customername,checknumber,paymentdate,amount from customers inner join payments using (customernumber);
END $$
DELIMITER ;
CALL getPayments();

0 replies

Ashritha-sd · 2022-03-15T07:54:14Z

Ashritha-sd
Mar 15, 2022

DELIMITER //

CREATE PROCEDURE getAllEmployees()
BEGIN
	SELECT concat(e.firstName," ",e.lastName),o.city,o.state,o.country
	FROM employees e INNER JOIN offices o USING(officeCode);
END //

DELIMITER ;
2.
DELIMITER //

CREATE PROCEDURE getPayments()
BEGIN
	SELECT c.customerName,p.checkNumber,p.paymentDate,p.amount 
	FROM customers c INNER JOIN payments p USING(customerNumber);
END //

DELIMITER ;

0 replies

lalit-ky · 2022-03-15T07:54:22Z

lalit-ky
Mar 15, 2022

DELIMITER//
CREATE PROCEDURE getEmployees()
BEGIN
    SELECT CONCAT(firstName," ",lastName) AS fullName,city,state,country
        FROM employees e INNER JOIN offices o USING (officeCode);
END//

DELIMITER ;


CALL getEmployees();
SHOW PROCEDURE STATUS LIKE 'getEmployees';

DELIMITER //
CREATE PROCEDURE getpayments()  
BEGIN
    SELECT customername,checknumber,paymentdate,amount FROM customers INNER JOIN payments USING (customernumber); 
END //

DELIMITER ;
call getpayments();

0 replies

meghvi-raj · 2022-07-05T10:46:25Z

meghvi-raj
Jul 5, 2022

--Q1
DELIMITER //

 CREATE PROCEDURE getEmployees() 
BEGIN
select concat(firstname, ' ', lastname),
    city,
    country
from employees
    inner join offices using (officecode);
END //

DELIMITER ;

call getEmployees();

--Q2

DELIMITER //

CREATE PROCEDURE getPayments() 
BEGIN
select customerName,
    checkNumber,
    paymentDate,
    amount
from customers
    inner join payments using (customerNumber);
END // 

DELIMITER;

call getPayments();

0 replies

uzma23 · 2022-07-05T10:49:05Z

uzma23
Jul 5, 2022

--1.
DELIMITER / / 
CREATE PROCEDURE getEmployees() 
BEGIN
select concat(firstname, ' ', lastname),
    city,
    state,
    country
from employees
    inner join offices using (officecode);
END 

/ / DELIMITER;

CALL getEmployees();

--2.
DELIMITER / / CREATE PROCEDURE getPayments() BEGIN
SELECT c.customerName,
    p.checkNumber,
    p.paymentDate,
    p.amount
from payments p,
    customers c
where c.customerNumber = p.customerNumber;
END

 / / DELIMITER;
 
CALL getPayments();

0 replies

gayatribabar · 2022-07-05T10:52:25Z

gayatribabar
Jul 5, 2022

DELIMITER //

create procedure getEmployees2(IN empNumber INT)
BEGIN
  select lastName, firstName, city, state, country from employees INNER JOIN offices using(officeCode) where employeeNumber=empNumber;
END //

DELIMITER ;

CALL getEmployees2(1002);

DELIMITER //

create Procedure getPaymentDetails2(IN customerN INT)
BEGIN
  select customerName, checkNumber, paymentDate, amount from customers inner join payments using(customerNumber) where customerNumber=customerN;
END //

delimiter ;

CALL `getPaymentDetails2`(103);

0 replies

vshvandana · 2022-07-05T11:01:08Z

vshvandana
Jul 5, 2022

delimiter//
create procedure getemployees( )
BEGIN

select CONCAT(firstName," ",lastname) as names, city, state, country from employees inner join offices using(officeCode);

end//
delimiter;
call getemployees();

 drop PROCEDURE getemployees;

-- .........................................................................................

 delimiter//
 Create procedure getpayments( )
 BEGIN
select customerName, checkNumber, paymentDate, amount from customers inner join payments using(customerNumber);
end//
delimiter;
call getpayments();

0 replies

anuj9604 · 2023-07-27T03:35:13Z

anuj9604
Jul 27, 2023

1. Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

Solution

delimiter $$

create procedure getallemployees()
begin
	select concat(firstName, " ", lastName) as fullName, city, state, country
	from employees
	left join offices using(officeCode);
end $$

delimiter ;

2. Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

Solution

delimiter $$

create procedure getPayments()
begin
	select customerName, checkNumber, paymentDate, amount
	from payments
	left join customers using(customerNumber);
end $$

delimiter ;

0 replies

niktwister · 2023-07-27T06:13:42Z

niktwister
Jul 27, 2023

1.Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS getEmployees()
BEGIN
    
    SELECT CONCAT(e.firstName,' ',e.lastName) AS name , o.city, o.state, o.country 
    FROM employees e
    INNER JOIN offices o USING(officeCode);

END $$

DELIMITER ;

2.Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS getPayments()
BEGIN
    
    SELECT c.customerName, p.checkNumber, p.paymentDate, p.amount
    FROM payments p
    INNER JOIN customers c USING(customerNumber);

END $$

DELIMITER ;

0 replies

apoorva-sin · 2023-07-27T06:17:12Z

apoorva-sin
Jul 27, 2023

-- Discussion Forum 10

delimiter $$

create procedure getEmployees(
	in id int
)

begin
	select concat(firstName, ' ', lastName) as Name, city, state, country from employees e 
	join offices o using (officeCode) 
	where e.employeeNumber = id;
end $$

delimiter ;

call getEmployees(1002);

0 replies

ashumore1998 · 2023-07-27T06:22:39Z

ashumore1998
Jul 27, 2023

--1. Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

delimiter //

create procedure getEmployees()
begin
select concat(firstName,' ',lastName) as full_name,city,state,country
from employees
join offices using (officecode);

end //

delimiter ;

call getEmployees();

--2. Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

delimiter //

create procedure getPayments()
begin
select c.customerName,checkNumber, paymentDate,amount
from customers c
join payments p using (customerNumber);

end //

delimiter ;

call getPayments();

0 replies

prathameshyadure · 2023-07-27T06:22:45Z

prathameshyadure
Jul 27, 2023

drop procedure getEmployees;
-- Q1
-- Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.
DELIMITER $$
create procedure getEmployees()
BEGIN
select firstname,o.city,o.state,o.country from employees e inner join offices o on e.officeCode=o.officeCode;
END $$
DELIMITER ;

call getEmployees();

-- Q2
-- Create a stored procedure named getPayments() that prints the following customer and payment info
-- :customerName, checkNumber, paymentDate, and amount.
delimiter $$
create procedure getPayments()
BEGIN
select c.customerName, checkNumber, paymentDate, amount from payments p inner join customers c on p.customerNumber = c.customerNumber;
END $$
DELIMITER ;

call getPayments();

0 replies

ManasFuturense · 2023-07-27T06:24:40Z

ManasFuturense
Jul 27, 2023

-- 1. Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

USE classicmodels;

DELIMITER $$
CREATE PROCEDURE getEmployees()
BEGIN
SELECT CONCAT(e.firstName, ' ', e.lastName) AS name, o.city, o.state, o.country
FROM employees e
JOIN offices o
ON e.officeCode = o.officeCode;
END $$
DELIMITER ;

CALL getEmployees();

SHOW CREATE PROCEDURE getEmployees;

SHOW PROCEDURE STATUS LIKE 'getEmployees';

-- 2. Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

DELIMITER $$
CREATE PROCEDURE getPayments()
BEGIN
SELECT c.customerName, p.checkNumber, p.paymentDate, p.amount
FROM customers c
JOIN payments p
USING (customerNumber);
END $$
DELIMITER ;

CALL getPayments()

0 replies

dhanyaMeruva9 · 2023-07-27T06:32:11Z

dhanyaMeruva9
Jul 27, 2023

--1
Delimiter $$
CREATE PROCEDURE getEmployees()
BEGIN
select concat(firstName,' ',lastName) as 'Full Name', city, state, country
from employees join offices using(officecode);
END
$$
Delimiter ;
call getEmployees();

--2
Delimiter $$
CREATE PROCEDURE getPayments()
BEGIN
select customername,checknumber,paymentdate,amount
from customers inner join payments using (customernumber);
END
$$
Delimiter ;
call getPayments();

0 replies

Shubham-Futurense · 2023-07-27T06:41:16Z

Shubham-Futurense
Jul 27, 2023

#1
DELIMITER $$
create procedure getEmployees()
BEGIN
SELECT concat(firstName,' ',lastName) as Name,city, state, country
FROM employees INNER JOIN offices USING (officeCode);
END
$$

DELIMITER ;

#2
DELIMITER $$
create procedure getPayments()
BEGIN
SELECT customerName, checkNumber, paymentDate, amount
FROM customers INNER JOIN payments USING (customerNumber);
END
$$

DELIMITER ;

0 replies

bijayjenafs · 2023-07-27T09:19:15Z

bijayjenafs
Jul 27, 2023

-- 1. Create a stored procedure named getEmployees() to display the following 
-- employee and their office info: name, city, state, and country.

delimiter //

create procedure getEmployees()
begin 
	select 
		concat(e.firstName, ' ', e.lastName) as name, o.city, o.state, o.country
	from employees e join offices o using (officeCode);
end //
delimiter ;

call getEmployees();

drop procedure getEmployees;

-- 2. Create a stored procedure named getPayments() that prints the following
--  customer and payment info:customerName, checkNumber, paymentDate, and amount.

delimiter //
create procedure getPayments()
begin
	select customerName, checkNumber, paymentDate, amount
    from payments
    join customers using (customerNumber);
end //
delimiter ;

call getPayments();

drop procedure getPayments;

0 replies

var5 · 2023-07-27T18:56:23Z

var5
Jul 27, 2023

Create a stored procedure named getEmployees() to display the following employee and
their office info : name, city, state, and country.

DELIMITER $$
CREATE PROCEDURE getEmployees()
BEGIN
SELECT CONCAT(firstname,' ',lastname) AS name, city, state, country
FROM employees INNER JOIN Offices USING(officecode);
END $$
DELIMITER;
CALL getEmployees();

Create a stored procedure named getPayments() that prints the following customer and
payment info : customerName, checkNumber, paymentDate, and amount.

DELIMITER %%
CREATE PROCEDURE getPayments()
BEGIN
SELECT customerName, checkNumber, paymentDate, amount
FROM customers INNER JOIN payments USING(customerNumber);
END %%
DELIMITER ;
CALL getPayments()

0 replies

NANDU-R-N · 2023-07-31T08:11:50Z

NANDU-R-N
Jul 31, 2023

#1.Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

CREATE PROCEDURE getEmployees(IN emp_name VARCHAR(255))
BEGIN
SELECT CONCAT(e.firstName,' ',e.lastName) AS name,o.city,o.state,o.country FROM employees e
JOIN offices o
ON e.officeCode = o.officeCode
WHERE e.firstName = emp_name;
END$$

#2.Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

CREATE PROCEDURE getPayments(IN cust_name VARCHAR(255))
BEGIN
SELECT c.customerName,p.checkNumber,p.paymentDate,p.amount FROM customers c
JOIN payments p
ON c.customerNumber = p.customerNumber
WHERE c.customerName = cust_name;
END $$

0 replies

Task - Stored Procedures - classicmodels DB #10

Uh oh!

Uh oh!

akash-coded Mar 15, 2022 Maintainer

Database = classicmodels

Dump file: classicmodels.sql

DB Schema

Stored Procedures

Replies: 51 comments

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

1. Create a stored procedure named getEmployees() to display the following employee and their office info: name, city, state, and country.

2. Create a stored procedure named getPayments() that prints the following customer and payment info:customerName, checkNumber, paymentDate, and amount.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

akash-coded
Mar 15, 2022
Maintainer

Database = `classicmodels`