Related questions

Procedure to display the Employees of a specific Department

2022-05-07 05:29 RAMCHANDRA 1DS19CS124 imported from Stackoverflow

Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

Name of the argument as Input : DeptNo

Write the code to create the procedure

Schema

I wrote the Query like this but i didnt get expected output

CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end

Expected output:

Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Why does this Sql query shows the possible changes but does not implement it?

    So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

    SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

    Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

  • how to get weeklytotal and yesterday record in mysql in one table

    Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

    Table name-dailydata-

    Sample data

    employee_id date total
    20 2022-04-25 10
    20 2022-04-26 20
    20 2022-04-27 20
    20 2022-04-28 10
    20 2022-04-29 20
    20 2022-04-30 30
    20 2022-04-31 40
    20 2022-05-01 50
    40 2022-04-26 20

    expected output

    employee_id weekly_total yesterday_record
    20 200 40
    40 20 0

    mysql query to get weekly data

    select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  • How to select distinct value

    I need to select all supervisor records. Our HR employee table set up like below

    Firstname Lastname Email Supervisor
    Frank Johns fjohns Taylor, Don
    Pat Hope phope Taylor, Don
    Jen Dow jdow Taylor, Don
    Taylor Don tdon Olson, Mike
    Kim Ronda kronda Olson, Mike
    Rob Smith rsmith Olson, Mike
    Mike Olson molson null

    The final result should be like this

    Firstname Lastname Email Supervisor
    Taylor Don tdon Olson, Mike
    Mike Olson molson null

    If I use Distinct on supervisor then it gives me a list of supervisor but not their info. Please help

  • SQL Join tables - join with last four digits of columns

    I'm trying to join two tables in SQL. I want to join on the column with the last four digits of a phone number with the a column from a second column where only the last four digits are shown.

    From ExcelSheet1 E
Join Walkers W
ON W.Lastname = E.LNAME
AND W.Firstname = E.FNAME
and W.PhoneNum like '%'+E.MEMBER_NUM
  • Facing issue while passing table name as input parameter in Oracle plsql. Not able to make this input as type table

    My procedure is as below.

    create or replace procedure load_table(tablename in varchar2)
as
begin
   declare 
      type t_list is table of ***tablename***%rowtype;
      lv_list t_list := t_list();
   begin
      select * bulk collect into lv_list from tablename;
   end;
end;

    How to pass the tablename which is passed as an input parameter to type table. As input is varchar and for %rowtype will come only for table.

  • HANA SQL Copy Data every month

    I'm trying to create a SQL procedure on HANA that will copy the most recent month's content of a table and append it to the end of the table. The below code does that but copies all content instead of the most recent month. For example I have January data and February data, and for March i only want to copy over February's.

    Update Procedure "SCHEMA"."PROC"()
Language SQLScript
as
Begin
    INSERT INTO "SCHEMA"."PROC"
    ("CUSTOMERNAME","QUANTITY", "COST", "ITEM", "MONTH")
    SELECT "CUSTOMERNAME","QUANTITY", "COST","ITEM", (TO_VARCHAR (NOW( 
), 'YYYY-MM-DD'))
    from "_SYS_BIC"."SCHEMA/PROC";

    End;

    TABLE EXAMPLE ----->

    customer1   50   220   dress   2022-01-01
customer2   10   220   shoes   2022-01-01
customer3   52   220   dress   2022-01-01
customer4   67   220   dress   2022-01-01
customer5   98   220   dress   2022-01-01
customer1   50   220   dress   2022-02-01
customer2   10   220   shoes   2022-02-01
customer3   52   220   dress   2022-02-01
customer4   67   220   dress   2022-02-01
customer5   98   220   dress   2022-02-01

    I want to copy over February's data (most recent month) ONLY..how can i do this?

  • Create a procedure to fill a new table

    I am new to PostgreSQL and am struggling with the creation of a procedure to save the data in a new table.

    This is how the empty table is created

    CREATE TABLE erp.tb_quarter 
(
    quarter integer NOT NULL,
    year integer NOT NULL,
    cust_no character(5) NOT NULL,
    iva_percent integer NOT NULL,
    amount real NOT NULL,

    CONSTRAINT pk_quarter
        PRIMARY KEY (quarter, year),

    CONSTRAINT fk_quarter
        FOREIGN KEY (cust_no)       
        REFERENCES erp.tb_customer (cust_no)
);

    The foreign key is associated with this table

    CREATE TABLE tb_customer
(
    cust_no CHAR(5) NOT NULL,
    cust_name VARCHAR(50) NOT NULL,
    cust_cif VARCHAR (15) NOT NULL,
    last_updated_by VARCHAR(20) NOT NULL DEFAULT 'SYSTEM',
    last_update_date DATE NOT NULL,
    CONSTRAINT pk_tb_customer PRIMARY KEY (cust_no)
);

    Other information can be extracted from this table:

    CREATE TABLE erp.tb_invoice 
(
    co_code            CHARACTER(3) NOT NULL,
    invoice_id         INT NOT NULL,
    invoice_no         CHARACTER VARYING(15)  NOT NULL,
    cust_no            CHARACTER(5) NOT NULL,
    site_id            INT NOT NULL,
    payed              CHARACTER(1) NOT NULL DEFAULT 'N',
    net_amount         REAL NOT NULL,
    iva_amount         REAL NOT NULL,
    tot_amount         REAL NOT NULL,
    last_updated_by    CHARACTER VARYING(20) DEFAULT 'SYSTEM',
    last_update_date   DATE NOT NULL,
    CONSTRAINT pk_invoice PRIMARY KEY (invoice_id),
    CONSTRAINT fk_invoice_company FOREIGN KEY (co_code) REFERENCES erp.tb_company (co_code),
    CONSTRAINT fk_invoice_customer FOREIGN KEY (cust_no) REFERENCES erp.tb_customer (cust_no),
    CONSTRAINT fk_invoice_site FOREIGN KEY (site_id) REFERENCES erp.tb_site (site_id)  
);

    This is how the procedure looks like. I want the procedure pr_calc_quarter to calculate the year and quarter, and insert the data in the table tb_quarter of all the customers, grouped by the iva type (VAT) for each client.

    CREATE OR REPLACE PROCEDURE pr_calc_quarter(integer, integer, character, integer, real) 
LANGUAGE 'plpgsql'      
AS $$

BEGIN
    SELECT
      extract(quarter from c.last_update_date) AS quarter,
      extract (year from c.last_update_date) AS year,
      c.cust_no,
      (i.iva_amount/i.tot_amount *100) AS iva_percent,
      i.tot_amount AS amount
        

    FROM
      erp.tb_customer AS c, 
      erp.tb_invoice AS i,
      erp.tb_quarter AS q
    
    WHERE
        i.cust_no = c.cust_no

    GROUP BY
       extract(quarter from c.last_update_date)
 

INSERT INTO erp.tb_quarter (quarter, year, cust_no, iva_percent, amount) VALUES ($1, $2, $3, $4, $5); 
COMMIT;

END;
$$;

    I am not sure if in this case a for loop is required so that I can check in each line the invoice of the customer and then sum the total amounts for each type of iva (VAT).

    Am I on the right way to get it?

    Thanks.